im trying to create two columns thats counting different items and then on the Third Column SUM up column 1 and 2. Any help is appreciated! Thanks!
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
You can use conditional aggregation and a subquery. The query looks something like this:
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]
コメントを追加