SUM totals - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

SUM totals

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2005-04-15 : 11:27:30

I'm trying to get a combined total of hours per employee going back 365 days.

If I do a standard Query for one employee :

SELECT
[AutoTACurrentPayDaily].[EmployeeID] AS 'EMPID',
[AutoTACurrentPayDaily].[PayDate] AS 'PAYDATE',
[AutoTACurrentPayDaily].[PayCode] AS 'PAYCODE',
[AutoTACurrentPayDaily].[PayHours] AS 'PAYHOURS'
FROM [AutoTACurrentPayDaily]
WHERE [AutoTACurrentPayDaily].[PayCode]IN('CTF', 'FML', 'FVF', 'IDF', 'IUF', 'PVF', 'SIF', 'SKF', 'VCF', 'VUF')
AND [AutoTACurrentPayDaily].[PayHours]>'0'
AND [AutoTACurrentPayDaily].[PayDate]<=[AutoTACurrentPayDaily].[PayPeriodEnd]
AND [AutoTACurrentPayDaily].[PayDate]>= GETDATE()-365
AND [AutoTACurrentPayDaily].[EmployeeID] = '00000186'
GROUP BY [AutoTACurrentPayDaily].[EmployeeID],
[AutoTACurrentPayDaily].[PayDate],
[AutoTACurrentPayDaily].[PayCode],
[AutoTACurrentPayDaily].[PayHours]
ORDER BY EMPID ASC,
PAYCODE ASC,
PAYDATE ASC

I get the following results :

EMPID PAYDATE PAYCODE PAYHOURS
00000186 2004-05-16 00:00:00.000 FML 8
00000186 2004-05-17 00:00:00.000 FML 8
00000186 2004-05-23 00:00:00.000 FML 8
00000186 2004-05-24 00:00:00.000 FML 8
00000186 2004-06-01 00:00:00.000 FML 8
00000186 2004-06-06 00:00:00.000 FML 8
00000186 2004-06-20 00:00:00.000 FML 8
00000186 2004-06-22 00:00:00.000 FML 8
00000186 2004-06-24 00:00:00.000 FML 8
00000186 2004-06-29 00:00:00.000 FML 8
00000186 2004-07-06 00:00:00.000 FML 8
00000186 2004-07-13 00:00:00.000 FML 8
00000186 2004-07-18 00:00:00.000 FML 8
00000186 2004-07-21 00:00:00.000 FML 8
00000186 2004-07-25 00:00:00.000 FML 8
00000186 2004-08-02 00:00:00.000 FML 8
00000186 2004-08-09 00:00:00.000 FML 8
00000186 2004-08-10 00:00:00.000 FML 8
00000186 2004-08-22 00:00:00.000 FML 8
00000186 2004-08-23 00:00:00.000 FML 8
00000186 2004-08-29 00:00:00.000 FML 8
00000186 2004-08-30 00:00:00.000 FML 8
00000186 2004-09-07 00:00:00.000 FML 8
00000186 2004-09-19 00:00:00.000 FML 8
00000186 2004-09-27 00:00:00.000 FML 8
00000186 2004-09-30 00:00:00.000 FML 8
00000186 2004-10-10 00:00:00.000 FML 8
00000186 2004-10-11 00:00:00.000 FML 8
00000186 2004-11-06 00:00:00.000 FML 5
00000186 2004-11-07 00:00:00.000 FML 8
00000186 2004-11-11 00:00:00.000 FML 8
00000186 2004-11-29 00:00:00.000 FML 8
00000186 2004-12-06 00:00:00.000 FML 8
00000186 2004-12-09 00:00:00.000 FML 8
00000186 2004-12-22 00:00:00.000 FML 8
00000186 2004-12-23 00:00:00.000 FML 8
00000186 2004-12-24 00:00:00.000 FML 8
00000186 2005-01-04 00:00:00.000 FML 8
00000186 2005-01-05 00:00:00.000 FML 8
00000186 2005-01-10 00:00:00.000 FML 4
00000186 2005-01-11 00:00:00.000 FML 4
00000186 2005-01-12 00:00:00.000 FML 4
00000186 2005-01-13 00:00:00.000 FML 4
00000186 2004-04-24 00:00:00.000 VCF 8
00000186 2004-04-25 00:00:00.000 VCF 8
00000186 2004-05-06 00:00:00.000 VCF 8
00000186 2004-05-09 00:00:00.000 VCF 8

Total hours for the employee = 357. I thought that to roll this all up to just the employee ID all I would need to do is remove the PayDate and PayCode :

SELECT
[AutoTACurrentPayDaily].[EmployeeID] AS 'EMPID',
SUM ([AutoTACurrentPayDaily].[PayHours]) AS 'PAYHOURS'
FROM [AutoTACurrentPayDaily]
WHERE [AutoTACurrentPayDaily].[PayCode]IN('CTF', 'FML', 'FVF', 'IDF', 'IUF', 'PVF', 'SIF', 'SKF', 'VCF', 'VUF')
AND [AutoTACurrentPayDaily].[PayHours]>'0'
AND [AutoTACurrentPayDaily].[PayDate]<=[AutoTACurrentPayDaily].[PayPeriodEnd]
AND [AutoTACurrentPayDaily].[PayDate]>= GETDATE()-365
AND [AutoTACurrentPayDaily].[EmployeeID] = '00000186'
GROUP BY [AutoTACurrentPayDaily].[EmployeeID],
[AutoTACurrentPayDaily].[PayHours]
ORDER BY EMPID ASC,
PAYHOURS ASC

But instead of just one total I get three :

EMPID PAYHOURS
00000186 5
00000186 16
00000186 336

Any reason why this would happen??

GC

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-04-15 : 11:48:38

You are grouping by PayHours:

GROUP BY [AutoTACurrentPayDaily].[EmployeeID],
[AutoTACurrentPayDaily].[PayHours]

CODO ERGO SUM

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources