Jibin Mathew Jibin Mathew -3 years ago 62
SQL Question

Use multiple SUM of COUNT in a single query

I have to find the sum of count values from a query and I did that like below

SELECT
SUM(Total) AS SUMV
FROM
(SELECT
COUNT(Session.SessionID) AS Total
FROM
[Session]
WHERE
DateCreated >='2016-03-20'
AND DateCreated <= '2016-03-22'
GROUP BY
CAST(DateCreated AS DATE)) s


Now I have to find the sum of count with a different condition and I tried to get the sum value as a second column of above table like below

SELECT
SUM(Total) AS SUMV, SUM(Total2) AS SUMV2
FROM
(SELECT
COUNT(Session.SessionID) AS Total
FROM
[Session]
WHERE
DateCreated >='2016-03-20'
AND DateCreated <= '2016-03-22'
GROUP BY
CAST(DateCreated AS DATE)) s,
(SELECT
COUNT(Session.SessionID) AS Total2
FROM
[Session]
WHERE
DateCreated >='2016-03-23'
AND DateCreated <= '2016-03-25'
GROUP BY
CAST(DateCreated AS DATE)) s2


But the SUM2 is not returning the correct
SUM
value. So is my query correct or any better way to use multiple instances of
SUM
of count in an SQL query

Answer Source

You can write the first query as:

SELECT COUNT(*)
FROM [Session] s
WHERE DateCreated >= '2016-03-20' AND  DateCreated <= '2016-03-22';

You can do multiple sums using conditional aggregation:

SELECT SUM(CASE WHEN DateCreated >= '2016-03-20' AND  DateCreated <= '2016-03-22'
            THEN 1 ELSE 0
           END) AS Total_1,
       SUM(CASE WHEN DateCreated >= '2016-03-23' AND  DateCreated <= '2016-03-25'
            THEN 1 ELSE 0
           END) AS Total_2           
FROM [Session] s;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download