Division of GROUP... - 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

SQL Server Development (2000)

Division of GROUP...

Author

Topic

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 13:04:56

My current SQL statement groups the records it finds but I need it to be able to form two groups based on the return of a column.

SELECT Prod_Code, SUM(Net) AS NetSum, SUM(Quantity) AS QuantSum, ShiftCode, MachineID, ENAME
FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code
WHERE ProductionDate = CONVERT(varchar(8), GETDATE(), 1) AND Len(Prod_Code) <= 4 GROUP BY ENAME, Prod_Code, ShiftCode, MachineID
ORDER BY Prod_Code

This is the current statement but I have to have it divide the results according to whether ShiftCode is equal to 1 or 2. Any thoughts?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 13:57:04

The CASE statement will do it:

SELECT Prod_Code,
CASE ShiftCode
WHEN 1 THEN 'One'
WHEN 2 THEN 'Two'
END AS Shift, SUM(Net) AS NetSum, SUM(Quantity) AS QuantSum, MachineID, ENAME
FROM traypack_scale.dbo.SPROD INNER JOIN Rawdata ON PCODE = Prod_Code
WHERE ProductionDate = CONVERT(varchar(8), GETDATE(), 1) AND Len(Prod_Code) <= 4
GROUP BY ENAME, Prod_Code,
CASE ShiftCode WHEN 1 THEN 'One' WHEN 2 THEN 'Two' END, MachineID
ORDER BY Prod_Code

Dorffius
Starting Member

36 Posts

Posted - 2002-01-31 : 14:19:02

I understand what your code does and it is working, but strangely. When I view the table using just a regular Select statement I can see ones and twos in the column but the results of the query with the CASE statement only return 'ONE's.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-01-31 : 14:28:28

The CASE statement is replacing the 1 and 2 with another expression. If you need to GROUP BY ShiftCode just remove the CASE statement entirely and put ShiftCode in it's place.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources