Cumulative counts with a GROUP BY? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Cumulative counts with a GROUP BY?

Author

Topic

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2014-06-06 : 14:38:41

Hope someone can help.

I have the following:


SELECT '201305' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 2)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID

UNION ALL

SELECT '201306' AS PAYPERIOD,
EMPLOYEE,
RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
COUNT (EMPCODE) AS BONUSCOUNT_YTD
FROM Table1
WHERE (YEAR = 2013 AND PERIOD < 3)
GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID

How can I get the counts to be cumulative? In other words, if an employee appears in pay period 201305 that's 1, if they then appear in pay period 201306 that becomes 2.

Hope this makes sense and many thanks for any advice.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-06 : 14:43:48

Maybe this:


SELECT EMPLOYEE, DEPARTMENT, SUM(BONUSCOUNT_YTD) AS BONUSCOUNT_YTD_CUMULATIVE
FROM
(
	SELECT '201305' AS PAYPERIOD,
	EMPLOYEE,
	RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
	COUNT (EMPCODE) AS BONUSCOUNT_YTD
	FROM Table1
	WHERE (YEAR = 2013 AND PERIOD < 2)
	GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID

	UNION ALL

	SELECT '201306' AS PAYPERIOD,
	EMPLOYEE,
	RIGHT ('000' + CAST (DEPT_ID AS VARCHAR(3)) ,3) AS DEPARTMENT,
	COUNT (EMPCODE) AS BONUSCOUNT_YTD
	FROM Table1
	WHERE (YEAR = 2013 AND PERIOD < 3)
	GROUP BY EMPCODE, YEAR, PERIOD, DEPT_ID
) t
GROUP BY EMPLOYEE, DEPARTMENT

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2014-06-06 : 17:04:48

This case screams for some sample data and expected outputs. For instance, if an employee is in multiple periods, did you want them to have multiple output records? Or just the last one?

Too often we enjoy the comfort of opinion without the discomfort of thought. - John F. Kennedy

Rasta Pickles
Posting Yak Master

174 Posts

Posted - 2014-06-07 : 08:26:21

I have no words to describe the generosity and sheer genius of you guys.

Tara, your reply worked like a dream, many many thanks :-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-06-08 : 12:56:21

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources