SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Cumulative counts with a GROUP BY?
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/06/2014 :  14:38:41  Show Profile  Reply with Quote
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

USA
36672 Posts

Posted - 06/06/2014 :  14:43:48  Show Profile  Visit tkizer's Homepage  Reply with Quote
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/
Go to Top of Page

Bustaz Kool
Flowing Fount of Yak Knowledge

USA
1645 Posts

Posted - 06/06/2014 :  17:04:48  Show Profile  Reply with Quote
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
Go to Top of Page

Rasta Pickles
Posting Yak Master

United Kingdom
171 Posts

Posted - 06/07/2014 :  08:26:21  Show Profile  Reply with Quote
I have no words to describe the generosity and sheer genius of you guys.

Tara, your reply worked like a dream, many many thanks :-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36672 Posts

Posted - 06/08/2014 :  12:56:21  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000