我试图创建两个计算不同项目的列,然后在第三列SUM的第一和第二列求和。谢谢!
SELECT
(SELECT COUNT(ecarrno) Conventional FROM L16T3
WHERE (l16lcode = 46) AND
(l46adr IN ('680', '657','693','623','639','704','644','679'))),
(SELECT COUNT(admunit) Auto FROM L16T3
WHERE (admunit= 16AP) AND
(l46adr IN ('611','618','637','638'))),
SUM (COUNT(ecarrno) + COUNT(admunit))
FROM L16T3
AND DATREG >= @('START DATE',datreg)
AND DATREG <= @('END DATE',datreg)
ORDER BY datreg
DESC,l16seqno DESC
您可以使用条件聚合和子查询。查询看起来像这样:
SELECT Conventional, Auto, (Conventional + Auto)
FROM (SELECT SUM(CASE WHEN l16lcode = 46 AND l46adr IN ('680', '657', '693', '623', '639', '704', '644', '679')
THEN 1 ELSE 0
END) as Conventional,
SUM(CASE WHEN admunit = '16AP' AND l46adr IN ('611', '618', '637', '638')
THEN 1 ELSE 0
END) as Auto
FROM L16T3 l
WHERE DATREG >= @('START DATE', datreg) AND DATREG <= @('END DATE', datreg)
) l
ORDER BY datreg ;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句