一个查询中的不同记录计数值 [英] 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

有2个表，例如tableA和tableB
计数因tableB.type而不同

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

预先感谢

推荐答案

子查询:

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

这篇关于一个查询中的不同记录计数值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！