Count query

[ronaldr]

I need to build a query for a report. The table I am reporting on has this kind of format:

name   | status | source   | ..... more fields
-------|--------|----------| -------------
aaa    | A      | SRC1     | ... values
bbb    | A      | SRC1     | ... values
ccc    | B      | SRC1     | ... values
ddd    | A      | SRC2     | ... values
eee    | B      | SRC2     | ... values
fff    | B      | SRC2     | ... values
ggg    | A      | SRC3     | ... values
hhh    | C      | SRC3     | ... values
iii    | C      | SRC3     | ... values

What I need to do is build a report that groups by the source field, and counts how many of each different statuses there are (I know how many possible statuses there are ahead of time).

Here is what the result should look like:

 source | countA | countB | countC |
--------|--------|--------|--------|
 SRC1   | 2      | 1      | 0      |
 SRC2   | 1      | 2      | 0      |
 SRC3   | 1      | 0      | 2      |

The query I came up with so far is like this:

_____________________

select 
   t.source,

   (select count(*) from tbl a 
    where a.source = t.source and status = 'A') as countA,

   (select count(*) from tbl b 
    where b.source = t.source and status = 'B') as countB,

   (select count(*) from tbl c 
    where c.source = t.source and status = 'C') as countC

from tbl t

group by source

___________________________


This query works, except that now I need to put a where clause in the outside statement, and this will throw off the counts unless i put the where clause on each of the subqueries.

Is there a better way to do this?

 

[bperry]

Try this?
(Untested...hopefully no syntax errorz)


select
t.source,
SUM(Case When Status ='A' Then 1 else 0 END) as CountA,
SUM(Case When Status ='B' Then 1 else 0 END) as CountB,
SUM(Case When Status ='C' Then 1 else 0 END) as CountC
from tbl t
group by t.source

 

[tlbroadbent]

The followig should solve your problem and be more more efficient.

select
t.source,
CountA=Sum(Case When t.Status 'A' Then 1 Else 0 End),
CountB=Sum(Case When t.Status 'B' Then 1 Else 0 End),
CountC=Sum(Case When t.Status 'C' Then 1 Else 0 End)
from tbl t
where <your criteria>
group by t.Source
Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[ronaldr]

Thanks a lot, that worked perfectly. And it looks SO much better.