CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
Krisha
Krisha

Reputation: 63

Total number calculations

I am writing a stored procedure with using following data and SQL. When I execute the below SQL, I am getting the count for every month, but I want the count to add up when I do for next month. I want to add an extra column to the query (Totalcount) and I'm expecting the results as shown below. Thanks in advance!

 Month_NUMBER  MonthlyCount  Totalcount
 --------------------------------------
  1              4             4
  2              1             5 
  3              1             6
  4              2             8

Here is the SQL I'm currently using:

drop table #test 

create table #test (name varchar(10), MON_NUMBER int)

insert into #test 
values ('XYZ', 1), ('ABC', 1), ('AZZ', 1), ('BCC', 1),
       ('HAS', 2), ('MRD', 3), ('GIV', 4), ('GIVE', 4)

SELECT 
    MON_NUMBER, 
    COUNT(NAME) AS MonthlyCount  
FROM 
    #test
GROUP BY 
    MON_NUMBER

Upvotes: 2

Views: 124

Answers (4)

Shiblu
Shiblu

Reputation: 477

@S.Yang has already given the best and proper answer. Since SUM() window function cannot be used in SQL Server 2008, you have to use self join. Based on his answer I am submitting another solution which is useful for huge datasets by not using CTE.

IF OBJECT_ID(N'dbo.temp', N'U') IS NOT NULL DROP TABLE dbo.temp;
SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount
INTO dbo.temp
FROM #test
GROUP BY MON_NUMBER;

-- Create Index
CREATE INDEX IX_dbo_temp ON dbo.temp (MON_NUMBER, MonthlyCount);

SELECT t1.MON_NUMBER, t1.MonthlyCount, SUM(t2.MonthlyCount) AS TotalCount
FROM dbo.temp AS t1
INNER JOIN dbo.temp AS t2 ON (t2.MON_NUMBER <= t1.MON_NUMBER)
GROUP BY t1.MON_NUMBER, t1.MonthlyCount;

-- Drop temporary tables
IF OBJECT_ID(N'dbo.temp', N'U') IS NOT NULL DROP TABLE dbo.temp;

Upvotes: 0

S.Yang
S.Yang

Reputation: 116

You can use a self-join in SQL Server 2008:

WITH CTE AS (
    SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount  
    FROM #test
    group by MON_NUMBER
)
SELECT C1.MON_NUMBER, C1.MonthlyCount, SUM(C2.MonthlyCount) AS TotalCount
FROM CTE C1
JOIN CTE C2 ON C1.MON_NUMBER >= C2.MON_NUMBER
GROUP BY C1.MON_NUMBER, C1.MonthlyCount
ORDER BY C1.MON_NUMBER, C1.MonthlyCount;

Upvotes: 1

Eli
Eli

Reputation: 2608

A window function would work here - give this a whirl.

SELECT DISTINCT
    MON_NUMBER, 
    COUNT(NAME) AS MonthlyCount, 
    COUNT(name) OVER (ORDER BY mon_number)
FROM #test
group by MON_NUMBER, NAME
Order by MON_NUMBER

Windowing documentation: https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

You can use your query as below:

Select *, Sum(MonthlyCount) over(order by Mon_nUmber) from (
    SELECT MON_NUMBER, COUNT(NAME) AS MonthlyCount  
    FROM #test
    group by MON_NUMBER
) a

Output as below:


+------------+--------------+------------+
| MON_NUMBER | MonthlyCount | TotalCount |
+------------+--------------+------------+
|          1 |            4 |          4 |
|          2 |            1 |          5 |
|          3 |            1 |          6 |
|          4 |            2 |          8 |
+------------+--------------+------------+

Upvotes: 2

Related Questions