SUM Trouble...Ha. - 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 Trouble...Ha.

Author

Topic

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-06-30 : 15:24:33

I have a large company database built and was trying to accomplish a query to compact the results. My data is built off of our company payroll paycodes and the data fields are broken down as follows: Station, Branch, Shop, Reporting Period Date, Paycode, Hours.

Rows of data look like this:

50 4401 2000PT 2002-01-31 00:00:00 CSO 88.4
50 4401 1000FT 2002-01-31 00:00:00 CSO 16.0
50 4401 1000FT 2002-01-31 00:00:00 CSW 15.3
50 4401 2000PT 2002-01-31 00:00:00 CSW 116.8
50 4401 1000FT 2002-01-31 00:00:00 DO .0
50 4401 1000FT 2002-01-31 00:00:00 FRL .2
50 4401 2000PT 2002-01-31 00:00:00 FRL .1
50 4401 1000FT 2002-01-31 00:00:00 HO 8.0
50 4401 1000FT 2002-01-31 00:00:00 HW 64.0
50 4401 2000PT 2002-01-31 00:00:00 HW 84.0
50 4401 2000PT 2002-01-31 00:00:00 HWE 19.7
50 4401 1000FT 2002-01-31 00:00:00 MPR 22.1
50 4401 2000PT 2002-01-31 00:00:00 MPR 20.1
50 4401 2000PT 2002-01-31 00:00:00 NBI 4.5
50 4401 2000PT 2002-01-31 00:00:00 NBO .0
50 4401 1000FT 2002-01-31 00:00:00 NM 3.0
50 4401 2000PT 2002-01-31 00:00:00 NM .5
50 4401 1000 2002-01-31 00:00:00 NM 1.0
50 4401 1000FT 2002-01-31 00:00:00 OMA 24.0
50 4401 1000 2002-01-31 00:00:00 OT .4
50 4401 1000FT 2002-01-31 00:00:00 OT 56.9
50 4401 2000PT 2002-01-31 00:00:00 OT 6.8
50 4401 1000FT 2002-01-31 00:00:00 PE 25.0
50 4401 1000 2002-01-31 00:00:00 PTE 2.0
50 4401 1000FT 2002-01-31 00:00:00 PTE 7.2
50 4401 2000PT 2002-01-31 00:00:00 PTE 415.9
50 4401 1000FT 2002-01-31 00:00:00 REG 1241.4
50 4401 1000 2002-01-31 00:00:00 REG 135.9
50 4401 2000PT 2002-01-31 00:00:00 REG 1702.5
50 4401 1000FT 2002-01-31 00:00:00 RL 1.8
50 4401 2000PT 2002-01-31 00:00:00 RL .2
50 4401 2000PT 2002-01-31 00:00:00 S2N 722.8
50 4401 2000PT 2002-01-31 00:00:00 S3N 1277.4
50 4401 2000PT 2002-01-31 00:00:00 S4N 240.6
50 4401 2000PT 2002-01-31 00:00:00 S5N 92.6
50 4401 1000FT 2002-01-31 00:00:00 SK 8.0
50 4401 2000PT 2002-01-31 00:00:00 SK 85.5
50 4401 2000PT 2002-01-31 00:00:00 SKC 76.5
50 4401 2000PT 2002-01-31 00:00:00 SKF 27.0
50 4401 1000FT 2002-01-31 00:00:00 TL 16.3
50 4401 2000PT 2002-01-31 00:00:00 TL 19.5
50 4401 1000 2002-01-31 00:00:00 TL 8.9
50 4401 2000PT 2002-01-31 00:00:00 TRO 3.1
50 4401 1000FT 2002-01-31 00:00:00 TRO 2.4
50 4401 1000 2002-01-31 00:00:00 VC 8.0
50 4401 1000FT 2002-01-31 00:00:00 VC 56.0
50 4401 2000PT 2002-01-31 00:00:00 VC 7.0

A years worth of data accounts for about 800,000 rows as a result of breaking things down to the "Shop" level. The company has many Stations that have many Branches inside each Station who have many Shops inside each Branch.

I am trying to compact this down to about 200,000 rows so that the data can be loaded into an Excel Pivot Table. (Our computers at the office aren't powerful enough to handle more than 300,000 rows. Comes back "out of memory" as the table is loading when I ship this data to the Analysts).

I tried what I thought would be a simple query of doing a SUM function of the hours by paycode only to the Profit Center / Branch Center levels but I keep getting all the rows again. Here is the query I tried.

SELECT losttime.Total_Hours_data.StationCode,
losttime.Total_Hours_data.BranchCode,
losttime.Total_Hours_data.ReportingPeriodDate,
losttime.Total_Hours_data.PayCode,
SUM (losttime.Total_Hours_data.Hours) AS Hours
FROM losttime.Total_Hours_data
WHERE DATEPART ( year,losttime.Total_Hours_data.ReportingPeriodDate)= '2002'
GROUP BY losttime.Total_Hours_data.StationCode,
losttime.Total_Hours_data.BranchCode,
losttime.Total_Hours_data.ReportingPeriodDate,
losttime.Total_Hours_data.PayCode,
losttime.Total_Hours_data.Hours
ORDER BY losttime.Total_Hours_data.StationCode,
losttime.Total_Hours_data.ReportingPeriodDate,
losttime.Total_Hours_data.PayCode

Result

50 4401 2002-01-31 00:00:00 CSO 88.4
50 4401 2002-01-31 00:00:00 CSO 16.0
50 4401 2002-01-31 00:00:00 CSW 116.8
50 4401 2002-01-31 00:00:00 CSW 15.3
etc....

If I try the SUM by Paycode I get an error as the Field is Text (VarChar).

The million dollar question is 'How do I get the "Hours" to total by paycode for each of the Branches at each Station'?

What I am trying to get is this result:

50 4401 2002-01-31 00:00:00 CSO 104.4
50 4401 2002-01-31 00:00:00 CSW 132.1
etc....

Help and Thanks.

GC

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-30 : 15:33:51

Remove losttime.Total_Hours_data.Hours
from the group by - that's the field you want to sum.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-06-30 : 15:34:19

You need to SUM(hours) and GROUP BY Station, Branch, Shop, Reporting Period Date, Paycode.

Jonathan
{0}

Gary Costigan
Yak Posting Veteran

95 Posts

Posted - 2003-07-01 : 10:37:30

Thank you to both of you for replying. Your response worked!!!

I knew it was something simple, sometimes you can't see the trees because of the forest. Doh!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources