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,NIGHT
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.
NIGHT1,NIGHT1PAYRATE,NIGHT
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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
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
>>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):
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
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.
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
I see now,
you have:
(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
it must be:
MAX(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
etc
you have:
(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
it must be:
MAX(case payrolcd WHEN 'NIGHT1' THEN 'NIGHT1' else '' end) as PAYCODE,
etc
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
GROUP BY EMPLOYID,CHEKDATE
order by chekdate,employid
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
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial