Different record count values in one query

Question

Hi,

My goal is to achieve a result set like the following

CODE | TOTAL1 | TOTAL2  
1    | 56     | 34  
2    | 12     | 15  
3    | 90     | 3

There are 2 tables e.g tableA and tableB
The counts are different by tableB.type

SELECT   code, COUNT (*) AS total1  
FROM tableA a
WHERE a.ID IN (select ID from tableB
    where type = 'XYZ')
GROUP BY code


SELECT   code, COUNT (*) AS total2  
FROM tableA a
WHERE a.ID IN (select ID from tableB
    where type = 'ABC')
GROUP BY code

I'd like to display the count for each code per type in the same query

Thanks in advance

Answer 1

Subqueries :

SELECT   code, (select COUNT (*) AS total1  
FROM tableA a1
WHERE a.ID IN (select ID from tableB
    where type = 'XYZ')
    and a1.code = tableA.code) as Total1,  
(select COUNT (*) AS total2  
FROM tableA a2
WHERE a.ID IN (select ID from tableB
    where type = 'ABC')
    and a2.code = tableA.code) as Total2)
from tableA
group by Code

Answer 2

No subquery

SELECT a.code,
sum(decode(b.type,'ABC',1,0)) AS total1,sum(decode(b.type,'XYZ',1,0)) AS total2
FROM tableA a
join tableB b on a.ID = b.ID
GROUP BY a.code

Regards
K

Answer 3

Probably one of many ways to skin it is to UNION the two in an in-line view and then select the sum of the counts, like this:

SELECT code, SUM(total1) total1, SUM(total2) total2 FROM ( SELECT code, COUNT() total1, 0 total2 FROM tableA a WHERE a.ID IN (select ID from tableB where type = 'XYZ') GROUP BY code UNION SELECT code, 0, COUNT ()
FROM tableA a WHERE a.ID IN (select ID from tableB where type = 'ABC') GROUP BY code ) GROUP BY code;