Jibin Mathew Jibin Mathew -3 years ago 65
SQL Question

Use multiple SUM of COUNT in a single querry

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 querry

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