Rollup Problem

[greg52]

I am have a problem with the ROLLUP operator. The table that I am querying is as such:

EMPLOYID EmplName Dept WCCode Gross SS MED FUTA SUTA

11000217 FLEURIMOND, ROSE 100002 NY8394 369.26 369.26 369.26 369.26 369.26
11000253 LAMBERT, JOY 100002 NY8394 299.12 299.12 299.12 299.12 299.12
11001076 DILEONE, ROBERT 100003 NY8394 149.56 149.56 149.56 149.56 149.56
11000807 FERRERA, ROB 100009 NY8394 506.12 506.12 506.12 506.12 506.12
11000966 JONES, CATHY 100009 NY8394 506.12 506.12 506.12 506.12 506.12


I am trying to total by Dept. My query is:

SELECT ISNULL(#tempTable.Employid, '') AS 'Employee ID', ISNULL(#tempTable.EmplName, '') AS Employee,
ISNULL(#tempTable.Dept, '') AS Department, ISNULL(#tempTable.WCCode, '') AS 'WC Code',
SUM(#tempTable.Gross) AS Gross, SUM(#tempTable.SS) AS 'FICA SS', SUM(#tempTable.Med) AS 'FICA Med',
SUM(#tempTable.FUTA) AS FUTA, SUM(#tempTable.SUTA) AS SUTA
from #tempTable
GROUP BY #tempTable.Dept, #tempTable.WCCode, #tempTable.EmplName, #tempTable.Employid WITH Rollup
#tempTable.EmplName
UNION ALL
SELECT ' ', ' ', ' ', 'Total: ', SUM(#tempTable.Gross), SUM(#tempTable.SS), SUM(#tempTable.Med), SUM(#tempTable.FUTA), SUM(#tempTable.SUTA)
FROM #tempTable

The output that I am getting is:

11000217 FLEURIMOND, ROSE 100002 NY8394 369.26 369.26 369.26 369.26 369.26
FLEURIMOND, ROSE 100002 NY8394 369.26 369.26 369.26 369.26 369.26
11000253 LAMBERT, JOY 100002 NY8394 299.12 299.12 299.12 299.12 299.12
LAMBERT, JOY 100002 NY8394 299.12 299.12 299.12 299.12 299.12
100002 NY8394 668.38 668.38 668.38 668.38 668.38
100002 668.38 668.38 668.38 668.38 668.38
11001076 DILEONE, ROBERT 100003 NY8394 149.56 149.56 149.56 149.56 149.56
DILEONE, ROBERT 100003 NY8394 149.56 149.56 149.56 149.56 149.56
100003 NY8394 149.56 149.56 149.56 149.56 149.56
100003 149.56 149.56 149.56 149.56 149.56
11000807 FERRERA, ROB 100009 NY8394 506.12 506.12 506.12 506.12 506.12
FERRERA, ROB 100009 NY8394 506.12 506.12 506.12 506.12 506.12
11000966 JONES, CATHY 100009 NY8394 506.12 506.12 506.12 506.12 506.12
JONES, CATHY 100009 NY8394 506.12 506.12 506.12 506.12 506.12
100009 NY8394 1012.24 1012.24 1012.24 1012.24 1012.24
100009 1012.24 1012.24 1012.24 1012.24 1012.24
1830.18 1830.18 1830.18 1830.18 1830.18
Total: 1830.18 1830.18 1830.18 1830.18 1830.18

The totals are correct, but, as can be seen, I'm getting a 2nd line for each employee.

The output I'm looking for is:
11000217 FLEURIMOND, ROSE 100002 NY8394 369.26 369.26 369.26 369.26 369.26
11000253 LAMBERT, JOY 100002 NY8394 299.12 299.12 299.12 299.12 299.12
100002 668.38 668.38 668.38 668.38 668.38
11001076 DILEONE, ROBERT 100003 NY8394 149.56 149.56 149.56 149.56 149.56
DILEONE, ROBERT 100003 NY8394 149.56 149.56 149.56 149.56 149.56
100003 149.56 149.56 149.56 149.56 149.56
11000807 FERRERA, ROB 100009 NY8394 506.12 506.12 506.12 506.12 506.12
FERRERA, ROB 100009 NY8394 506.12 506.12 506.12 506.12 506.12
11000966 JONES, CATHY 100009 NY8394 506.12 506.12 506.12 506.12 506.12
JONES, CATHY 100009 NY8394 506.12 506.12 506.12 506.12 506.12
100009 1012.24 1012.24 1012.24 1012.24 1012.24
Total: 1830.18 1830.18 1830.18 1830.18 1830.18

Can anyone help me with the Select statement? Thank you.

 

[gmmastros]

This isn't actual data is it. With payroll information. If it is, I suggest you red flag your post. I'd hate to be one of those people, do a google search on my own name, and see my payroll information on the internet.

In the future, I urge you to change data when posting to protect the innocent.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

If you do decide to red flag this, create a new post, but use dummy names like Homer Simpson, Marge Simpson, etc...

You have a valid question that we can problably help with.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[greg52]

George - The data was changed to protect the innocent!

 

[icemel]

making up names such as these is what makes our day to day tasks as dba's exciting

 

[gmmastros]

I've never had much luck with Rollup, so I always find another way. Like this...

Create 
Table 	#tempTable
		(EMPLOYID Integer,
		EmplName VarChar(50),
		Dept Integer,
		WCCode VarChar(20),
		Gross Decimal(10,4),
		SS Decimal(10,4),
		MED Decimal(10,4),
		FUTA Decimal(10,4),
		SUTA Decimal(10,4)
		)


Insert Into #tempTable Values(11000217,'FLEURIMOND, ROSE',100002,'NY8394',369.26,369.26,369.26,369.26,369.26)
Insert Into #tempTable Values(11000253,'LAMBERT, JOY    ',100002,'NY8394',299.12,299.12,299.12,299.12,299.12)
Insert Into #tempTable Values(11001076,'DILEONE, ROBERT ',100003,'NY8394',149.56,149.56,149.56,149.56,149.56)
Insert Into #tempTable Values(11000807,'FERRERA, ROB    ',100009,'NY8394',506.12,506.12,506.12,506.12,506.12)
Insert Into #tempTable Values(11000966,'JONES, CATHY    ',100009,'NY8394',506.12,506.12,506.12,506.12,506.12)

Select	[Employee ID], 
		Employee, 
		Department, 
		[WC Code],
		Gross, 
		[FICA SS], 
		[FICA Med], 
		FUTA, 
		SUTA
From	(
		SELECT 	1 As SortOrder,
				ISNULL(#tempTable.Employid, '') AS 'Employee ID', 
				ISNULL(#tempTable.EmplName, '') AS Employee, 
				ISNULL(#tempTable.Dept, '') AS Department, 
				ISNULL(#tempTable.WCCode, '') AS 'WC Code',
				SUM(#tempTable.Gross) AS Gross, 
				SUM(#tempTable.SS) AS 'FICA SS', 
				SUM(#tempTable.Med) AS 'FICA Med', 
				SUM(#tempTable.FUTA) AS FUTA, 
				SUM(#tempTable.SUTA) AS SUTA
		from 	#tempTable
		GROUP BY #tempTable.Dept, 
				#tempTable.WCCode, 
				#tempTable.EmplName, 
				#tempTable.Employid  
		
		UNION ALL
		
		SELECT 	2 As SortOrder,
				'' AS 'Employee ID', 
				'' AS Employee, 
				ISNULL(#tempTable.Dept, '') AS Department, 
				ISNULL(#tempTable.WCCode, '') AS 'WC Code',
				SUM(#tempTable.Gross) AS Gross, 
				SUM(#tempTable.SS) AS 'FICA SS', 
				SUM(#tempTable.Med) AS 'FICA Med', 
				SUM(#tempTable.FUTA) AS FUTA, 
				SUM(#tempTable.SUTA) AS SUTA
		from 	#tempTable
		GROUP BY #tempTable.Dept, 
				#tempTable.WCCode
		
		UNION ALL
		SELECT 3, ' ', ' ', ' ', 'Total: ', SUM(#tempTable.Gross), SUM(#tempTable.SS), SUM(#tempTable.Med), SUM(#tempTable.FUTA), SUM(#tempTable.SUTA) 
		FROM #tempTable
		) A
Order By Case When Department = 0 then 9999999999 Else Department End, SortOrder

Drop Table #tempTable

Hope this helps.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[greg52]

That did it. Thanks, George.

 

[gmmastros]

You're welcome.

I hope you didn't mis-understand regarding the name issue. I was just being careful.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[greg52]

No offense taken. Even though I altered the names, they do appear to be real.