Using sub-query to count specific rows

Question

I have a query that I am using for a report. Each line is a row of data containing a medical history, the client_id is repeated for each medical history.

I'm grouping by client_id and summing their conditions but I want to use a sub-query to find any conditions that are above a preset score. My current query is giving me the total for the whole table, not just the specific client_id.

Can someone help me out? Thanks!

Here my query:

select 
    DATEADD(month, DATEDIFF(month, 0, taken_on), 0), 
    client_id, 
    count(hscore), 
    sum(hscore),
    (select count(hscore) 
            from amexmedscreen 
            where hscore >= '3.5') 
    from amexmedscreen 
    group by taken_on, client_id`

So you want count(hscore) for each client_id and taken_on, but in that last value you only want to count hscores >= 3.5? — jtpereyda
– jtpereyda, Commented Mar 9, 2011 at 16:23

Peter Lang · Accepted Answer · 2011-03-09 20:05:09Z

6

It should be sufficient to use CASE to get 1 for relevant rows, and sum those:

select 
    ...
    sum(hscore),
    sum(CASE WHEN hscore >= 3.5 THEN 1 ELSE 0 END)
group by taken_on, client_id`

edited Mar 9, 2011 at 20:05

answered Mar 9, 2011 at 16:24

Peter Lang

55.7k29 gold badges153 silver badges165 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Winston Smith Over a year ago

I think he wants the sum to be relative to the client aggregation only, not client and taken_on

Peter Lang Over a year ago

@WinstonSmith: You might be right, but it's difficult to tell. The OP only mentions grouping by clientid even though the query groups by client_id and taken_on. In the other case i think I'd first do that select, only grouping by client_id, and nest it in another query grouping by taken_on and client_id.

Andriy M Over a year ago

END is knocking on the door and yelling 'Let me in!', but no one seems to hear...

Peter Lang Over a year ago

@AndriyM: Thanks for noticing that poor thing, fixed ;)

15ee8f99-57ff-4f92-890c-b56153 · Accepted Answer · 2011-03-09 16:57:12Z

1

Your subquery is a separate query. It's not constrained by what's going on in the main query. You need to tell it to do the count only for the client_id of the current record in the outer query. You'll be referring to the same table twice in two different queries, so you'll have to use a different alias for each one.

Something like this should work:

select DATEADD(month, DATEDIFF(month, 0, taken_on), 0), 
     client_id, 
     count(hscore), 
     sum(hscore),
     (select count(hscore) 
      from amexmedscreen subq 
      where 
          hscore >= '3.5' 
          and subq.client_id = outerq.client_id )
from amexmedscreen outerq
group by taken_on, client_id

edited Mar 9, 2011 at 16:57

answered Mar 9, 2011 at 16:24

15ee8f99-57ff-4f92-890c-b56153

37.1k7 gold badges68 silver badges123 bronze badges

Comments

Jean-Christophe Fortin · Accepted Answer · 2011-03-09 16:22:47Z

0

select DATEADD(month, DATEDIFF(month, 0, taken_on), 0), 
client_id, 
count(hscore), 
sum(hscore),
tb1.COUNT_HSCORE
from amexmedscreen 
INNER JOIN 
(select client_id, count(hscore) as COUNT_HSCORE
 from amexmedscreen 
 where hscore >= '3.5' 
 group by client_ID) as tb1
ON tb1.client_id = client id
group by taken_on, client_id,tb1.COUNT_HSCORE

answered Mar 9, 2011 at 16:22

Jean-Christophe Fortin

7486 silver badges17 bronze badges

Collectives™ on Stack Overflow

Using sub-query to count specific rows

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related