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.

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

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.

then, why do you use MAX(EMPLOYID) and max(CHEKDATE)

remove the MAX() function for the 2 fields

```
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
```

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
```

## Premium Content

You need an Expert Office subscription to comment.Start Free Trial