I need to build a query for a report. The table I am reporting on has this kind of format:
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:
The query I came up with so far is like this:
_____________________
___________________________
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?
Code:
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:
Code:
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:
_____________________
Code:
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?