T-SQL Query to Get SUM(COUNT())

Question

Post reply

T-SQL Query to Get SUM(COUNT())

jain.ashish21

SSC Veteran

Points: 291
More actions
May 19, 2010 at 5:31 am

#221264

Hi,
I am planning to get a report for a table with following table structure:
ID RequestDate
-----------------------------
1 2010/01/01
2 2010/02/14
3 2010/03/20
4 2010/01/07
5 2009/03/31
I want the results as:
ID_Count RequestDate Sum
-----------------------------------------
2 2010/01 2
1 2010/02 3
2 2010/03 5
Pls help.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Paul White SSC Guru Points: 150468 More actions · Answer 1

DECLARE @Sample

TABLE (

ID INTEGER NOT NULL PRIMARY KEY,

RequestDate DATE NOT NULL

);

INSERT @Sample (ID, RequestDate)

VALUES (1, '2010-01-01'),

(2, '2010-02-14'),

(3, '2010-03-20'),

(4, '2010-01-07'),

(5, '2009-03-31');

WITH Summary

AS (

SELECT iTVF.RequestMonth,

ID_Count = COUNT_BIG(ID)

FROM @Sample S

CROSS

APPLY (

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01')

) iTVF (RequestMonth)

GROUP BY

iTVF.RequestMonth

)

SELECT RequestMonth = CONVERT(CHAR(7), S1.RequestMonth, 111),

S1.ID_Count,

iTVF.ID_Sum

FROM Summary S1

CROSS

APPLY (

SELECT ID_Sum = SUM(S2.ID_Count)

FROM Summary S2

WHERE S2.RequestMonth <= S1.RequestMonth

) iTVF;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

Hi, This is my first attempt at running totals, hope i am not screwing up anything!

Now setting up the test environtment:

SET DATEFORMAT YMD

IF OBJECT_ID('TEMPDB..#A') IS NOT NULL

DROP TABLE #A

CREATE TABLE #A

(

ID int,

Date SMALLDATETIME

)

insert into #A (ID,Date)

select 1,'2010/01/01' union all

select 2,'2010/02/14' union all

select 3,'2010/03/20' union all

select 4,'2010/01/07' union all

select 5,'2010/03/31'

Now the real code; note each line is important here!

IF OBJECT_ID('TEMPDB..#temp') IS NOT NULL

DROP TABLE #temp

;with cte as (

select Datepart(mm,Date) monthpart , count(*) ct , null [sum] from #A

GROUP BY Datepart(mm,Date)

)

select Row_number() over(order by monthpart) rn, * into #temp from cte

DECLARE @N INT, @Anchor INT

SELECT @N = 0

update a

set @N = [sum] = @N + ct , --Adds 3 to N and updates SomeInt with N

@Anchor = rn

FROM #temp a --WITH (TABLOCKX) --Can't be used on a table variable

OPTION (MAXDOP 1)

select '2010/'+STUFF('00',LEN('00')-LEN(CONVERT(VARCHAR,monthpart))+1,LEN(monthpart),monthpart) date

, ct, [sum]

from #temp

Hope this helps you! There will be lot of other alternatives! lets wait for other to join in!

jain.ashish21 SSC Veteran Points: 291 More actions · Answer 3

Thanks for the quick replies. Apologies for not letting you know, I have SQL Server 2000 as DB. These queries are SQL 2005 specific I guess.

Paul White SSC Guru Points: 150468 More actions · Answer 4

jain.ashish21 (5/19/2010)
Thanks for the quick replies. Apologies for not letting you know, I have SQL Server 2000 as DB. These queries are SQL 2005 specific I guess.

Awesome :doze:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Paul White SSC Guru Points: 150468 More actions · Answer 5

As far as I can remember, this will work in SQL Server 2000. I don't have it installed any more, so no guarantees!

DECLARE @Sample

TABLE (

ID INTEGER NOT NULL PRIMARY KEY,

RequestDate DATETIME NOT NULL

);

INSERT @Sample (ID, RequestDate) VALUES (1, '2010-01-01');

INSERT @Sample (ID, RequestDate) VALUES (2, '2010-02-14');

INSERT @Sample (ID, RequestDate) VALUES (3, '2010-03-20');

INSERT @Sample (ID, RequestDate) VALUES (4, '2010-01-07');

INSERT @Sample (ID, RequestDate) VALUES (5, '2009-03-31');

DECLARE @Summary

TABLE (

RequestMonth DATETIME PRIMARY KEY,

ID_Count INTEGER NOT NULL

);

INSERT @Summary (RequestMonth, ID_Count)

SELECT DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01'),

COUNT(*)

FROM @Sample S

GROUP BY

DATEADD(MONTH, DATEDIFF(MONTH, '2010-01-01', S.RequestDate), '2010-01-01');

SELECT RequestMonth = CONVERT(CHAR(7), S1.RequestMonth, 111),

S1.ID_Count,

ID_Sum =

(

SELECT SUM(S2.ID_Count)

FROM @Summary S2

WHERE S2.RequestMonth <= S1.RequestMonth

)

FROM @Summary S1;

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

jain.ashish21 SSC Veteran Points: 291 More actions · Answer 6

The above query did resolve what I was looking for. But now I have a bit different scenario. Here's my table structure

ID LoginDate RemovalDate

----------------------------------------

1 2009/08/01 NULL

2 2009/09/12 2010/01/02

3 2009/08/31 2009/10/29

4 2010/02/17 NULL

5 2009/10/18 2009/11/22

I want a consolidated results of how many ID's were not removed in a particular month. So the result set should be

Date NotRemoved_ID

--------------------------

2009/08 2

2009/09 3

2009/10 3 [One ID got removed in 2009/10]

2010/02 2 [Two got removed in 2009/11 and 2010/01]

Thanks for the help.

Paul White SSC Guru Points: 150468 More actions · Answer 7

jain.ashish21 (5/20/2010)
The above query did resolve what I was looking for. But now I have a bit different scenario. Here's my table structure

So you think because I converted your printed structures to code once that I'm going to do it every time?

Put a bit of effort in.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

T-SQL Query to Get SUM(COUNT())

Cookies on SQLServerCentral