CODEWITHSUNDEEP
sqloracle10g
db83
db83

Reputation: 169

Different record count values in one query

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

Upvotes: 0

Views: 1051

Answers (3)

Anton
Anton

Reputation: 11

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;

Upvotes: 1

Khb
Khb

Reputation: 1433

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

Upvotes: 3

Mongus Pong
Mongus Pong

Reputation: 11477

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

Upvotes: 1

Related Questions