我如何在同一查询中减去两个计数选定列?

Question

我有一个SQL查询，正在计算表中的项目数量并按省份名称对它们进行分组。我有另一个表，我加入，有一个列的上传，只显示已上传的项目。我想通过减去这两列来得到待挂项。

这是我的数据

totallabs   LabUploaded
147           0
150           0
156           0
273           0
176           0
244           1
38            1
131           0
197           1
133           1
124           0

这就是我的目标

totallabs   LabUploaded  Pending
147           0           147
150           0           150
156           0           156
273           0           273
176           0           176
244           1           243
38            1           37
131           0           131
197           1           196
133           1           132
124           0           124

这是对第一个表

的查询

select count(l.facility_name) as totallabs,
count(u.lab_code) as LabUploaded
from labs l 
left join upload u on  u.lab_code = l.mfl_code
inner join province p on p.id = l.province_code
group by p.province_name

我试图使用SubQueryAlias给我的数据，但没有得到正确的数据，

select  SubQueryAlias.*
,       totallabs - LabUploaded as pending
from    (
select  (
select count(l.facility_name) 
from   labs l
) as totallabs,            
(
select  count(u.lab_code) 
from    upload u
) as LabUploaded 
from    labs l
left join upload u on  u.lab_code = l.mfl_code 
inner join districts p on p.id = l.district_code
group by p.district_name
) as SubQueryAlias

如何在两个计数列之间进行相减并获得挂起的数据?任何帮助都是感激的

Answer 1

下次请给我们提供一些样本数据，以及表格，您可以使用SQL Fiddle来简化。

检查这是否符合您的需要:

SELECT province_code,
COUNT(labs.mfl_code) TotalLabs,
COUNT(uploads.lab_code) Uploads,
COUNT(labs.mfl_code) - COUNT(uploads.lab_code) Pending
FROM labs
LEFT join uploads ON uploads.lab_code = labs.mfl_code
GROUP BY province_code

SQl Fiddle示例:http://sqlfiddle.com/!9/c689e7/1

如果你觉得我的答案有用，如果你投票并标记为接受，我将感激不尽。

Answer 2

我将忽略注释的问题。实现目标的一种简单方法是将工作查询放入CTE，然后通过从中进行选择来计算差值。例如,

with cte as (
select count(labs.facility_name) as totallabs,
count(u.lab_code) as LabUploaded
from dbo.labs as labs 
left join dbo.upload as upl on  upl.lab_code = labs.mfl_code
inner join dbo.province as prov on prov.id = labs.province_code
group by prov.province_name
)
select cte.*, cte.totallabs - cte.LabUploaded as pending
from cte
order by ...;

注意细微的添加。单字母别名可能令人困惑。对表名进行模式限定。行顺序通常很重要，这需要使用Order BY子句。使用语句终止符。养成好习惯。