CODEWITHSUNDEEP
sqlsql-server
Sebastian
Sebastian

Reputation: 4811

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

Upvotes: 0

Views: 78

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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;

Upvotes: 1

Related Questions