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

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