Avatar of rwheeler23
rwheeler23Flag for United States of America

asked on 

SQL Group By Syntax SQL Server 2005

I am trying to rearrange this script so that it will have one column for each type of pay code per record. In the database each pay record is on a separate record. The original query did not include the group by and it works, however each record is on a separate line. It would be easier for the user if the 6 possible pay codes were on the same line. The end result I am looking for is:

NIGHT1,NIGHT1PAYRATE,NIGHT2,NIGHT2PAYRATE,VACATN,VACATNPAYRATE  ETC...

I keep getting a message about the payrolcd is not in the group by. I do not want the payrolcd in the group by. It is grouped by checkdate and employid.


select MAX(employid),MAX(chekdate),
case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end  as PAYCODE,
case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end  as NIGHT1HOURS,
case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end  as NIGHT1RATE,
case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end  as NIGHT1TOTAL,
case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end  as PAYCODE,
case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end  as NIGHT2HOURS,
case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end  as NIGHT2RATE,
case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end  as NIGHT2TOTAL,
case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end  as PAYCODE,
case payrolcd WHEN 'VACATN' THEN untstopy else 0 end  as VACATNHOURS,
case payrolcd WHEN 'VACATN' THEN payrate  else 0 end  as VACATNRATE,
case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end  as VACATNTOTAL,
case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end  as PAYCODE,
case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end  as SICKTMHOURS,
case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end  as SICKTMRATE,
case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end  as SICKTMTOTAL,
case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end  as PAYCODE,
case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end  as HOLIDYHOURS,
case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end  as HOLIDYRATE,
case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end  as HOLIDYTOTAL,
case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end  as PAYCODE,
case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end  as PERSNLHOURS,
case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end  as PERSNLRATE,
case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid

Open in new window

Microsoft SQL Server 2005SQL

Avatar of undefined
Last Comment
Anthony Perkins
Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image


You are using and aggregate function (MAX) so any other field included in the field list must be in a GROUP BY clause
>>I do not want the payrolcd in the group by. It is grouped by checkdate and employid.<<
Actually the problem is not payrolcd, but rather what it is you want to do with the CASE statement since it is not in an aggregate function.

>GROUP BY EMPLOYID,CHEKDATE

then, why do you use MAX(EMPLOYID) and max(CHEKDATE)
remove the MAX() function for the 2 fields
If I was to guess, I suspect you want something like this:
select MAX(employid),MAX(chekdate),
SUM(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end)  as NIGHT1HOURS,
SUM(case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end)  as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end)  as NIGHT1TOTAL,
SUM(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end)  as NIGHT2HOURS,
SUM(case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end)  as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end)  as NIGHT2TOTAL,
SUM(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end)  as VACATNHOURS,
SUM(case payrolcd WHEN 'VACATN' THEN payrate  else 0 end)  as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end)  as VACATNTOTAL,
SUM(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end)  as SICKTMHOURS,
SUM(case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end)  as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end)  as SICKTMTOTAL,
SUM(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end)  as HOLIDYHOURS,
SUM(case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end)  as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end)  as HOLIDYTOTAL,
SUM(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end)  as PERSNLHOURS,
SUM(case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end)  as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end)  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid

Open in new window

>>why do you use MAX(EMPLOYID) and max(CHEKDATE)<<
Good point, perhaps the table is not normalized.  Which also tells me that if that is the case the query I posted should be (fix the obvious typo with xselect):

xselect employid, chekdate,
SUM(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end)  as NIGHT1HOURS,
SUM(case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end)  as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end)  as NIGHT1TOTAL,
SUM(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end)  as NIGHT2HOURS,
SUM(case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end)  as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end)  as NIGHT2TOTAL,
SUM(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end)  as VACATNHOURS,
SUM(case payrolcd WHEN 'VACATN' THEN payrate  else 0 end)  as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end)  as VACATNTOTAL,
SUM(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end)  as SICKTMHOURS,
SUM(case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end)  as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end)  as SICKTMTOTAL,
SUM(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end)  as HOLIDYHOURS,
SUM(case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end)  as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end)  as HOLIDYTOTAL,
SUM(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end)  as PERSNLHOURS,
SUM(case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end)  as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end)  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid

Open in new window

Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

The untstopy and uprtrxam are the two fields that need to be summed. When I run the code below I get the messsage that payrolcd is not included in the Group By clause.

The bottom line to this is that is possible that one employee could put in hours for any one of these six pay codes on the same day. What I am trying to do is for each employee for each day worked, show the total number of hours worked for each pay code. That is why I am grouping by employee ID and CHEKDATE.
select employid,chekdate,
(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end)  as NIGHT1HOURS,
(case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end)  as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end)  as NIGHT1TOTAL,
(case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end)  as NIGHT2HOURS,
(case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end)  as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end)  as NIGHT2TOTAL,
(case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end)  as VACATNHOURS,
(case payrolcd WHEN 'VACATN' THEN payrate  else 0 end)  as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end)  as VACATNTOTAL,
(case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end)  as SICKTMHOURS,
(case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end)  as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end)  as SICKTMTOTAL,
(case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end)  as HOLIDYHOURS,
(case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end)  as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end)  as HOLIDYTOTAL,
(case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end)  as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end)  as PERSNLHOURS,
(case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end)  as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end)  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Avatar of rwheeler23
rwheeler23
Flag of United States of America image

ASKER

select employid,chekdate,
MAX((case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT1' THEN untstopy else 0 end)  as NIGHT1HOURS,
MAX((case payrolcd WHEN 'NIGHT1' THEN payrate  else 0 end))  as NIGHT1RATE,
SUM(case payrolcd WHEN 'NIGHT1' THEN uprtrxam else 0 end)  as NIGHT1TOTAL,
MAX((case payrolcd WHEN 'NIGHT2' THEN 'NIGHT2' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'NIGHT2' THEN untstopy else 0 end)  as NIGHT2HOURS,
MAX((case payrolcd WHEN 'NIGHT2' THEN payrate  else 0 end))  as NIGHT2RATE,
SUM(case payrolcd WHEN 'NIGHT2' THEN uprtrxam else 0 end)  as NIGHT2TOTAL,
MAX((case payrolcd WHEN 'VACATN' THEN 'VACATN' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'VACATN' THEN untstopy else 0 end)  as VACATNHOURS,
MAX((case payrolcd WHEN 'VACATN' THEN payrate  else 0 end))  as VACATNRATE,
SUM(case payrolcd WHEN 'VACATN' THEN uprtrxam else 0 end)  as VACATNTOTAL,
MAX((case payrolcd WHEN 'SICKTM' THEN 'SICKTM' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'SICKTM' THEN untstopy else 0 end)  as SICKTMHOURS,
MAX((case payrolcd WHEN 'SICKTM' THEN payrate  else 0 end))  as SICKTMRATE,
SUM(case payrolcd WHEN 'SICKTM' THEN uprtrxam else 0 end)  as SICKTMTOTAL,
MAX((case payrolcd WHEN 'HOLIDY' THEN 'HOLIDY' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'HOLIDY' THEN untstopy else 0 end)  as HOLIDYHOURS,
MAX((case payrolcd WHEN 'HOLIDY' THEN payrate  else 0 end))  as HOLIDYRATE,
SUM(case payrolcd WHEN 'HOLIDY' THEN uprtrxam else 0 end)  as HOLIDYTOTAL,
MAX((case payrolcd WHEN 'PERSNL' THEN 'PERSNL' else '' end))  as PAYCODE,
SUM(case payrolcd WHEN 'PERSNL' THEN untstopy else 0 end)  as PERSNLHOURS,
MAX((case payrolcd WHEN 'PERSNL' THEN payrate  else 0 end))  as PERSNLRATE,
SUM(case payrolcd WHEN 'PERSNL' THEN uprtrxam else 0 end)  as PERSNLTOTAL
FROM UPR30300
where payrolcd in ('NIGHT1','NIGHT2','VACATN','SICKTM','HOLIDY','PERSNL') and EMPLOYID='34328'
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
As usual, you folks always come through. Thanks for your help. This is the final version that gives me what I need.

Open in new window

You are welcome.

:(
Microsoft SQL Server 2005
Microsoft SQL Server 2005

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

72K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo