We help IT Professionals succeed at work.

SQL Group By Syntax SQL Server 2005

rwheeler23
rwheeler23 used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:

You are using and aggregate function (MAX) so any other field included in the field list must be in a GROUP BY clause
Top Expert 2012

Commented:
>>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.

Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
>GROUP BY EMPLOYID,CHEKDATE

then, why do you use MAX(EMPLOYID) and max(CHEKDATE)
remove the MAX() function for the 2 fields
Top Expert 2012

Commented:
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

Top Expert 2012

Commented:
>>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

rwheeler23President

Author

Commented:
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

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
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
rwheeler23President

Author

Commented:
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

Top Expert 2012

Commented:
You are welcome.

:(