question

smeachum avatar image
smeachum asked

Use of an aggregate in subquery select

I have two tables containing employee timesheet records. All employees have summary records in the Time table. Some also have detailed records in a Time_Det table. I need all records from Time_Det plus any records from the Time table when no detail records exist for the employee and work date. I used a union query to compile all the records. Linking the two tables directly resulted in duplications where detail records exist. When I used the result of the union query as a view, I was able to further select the desired records using a second query. I want to be able to do both steps in one query but keep getting "An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference." Here is my code: select cempid, dworkdate, mytype, npaycode, DEPT, JOB, reghrs, othrs, typecnt from ( SELECT cempid, dworkdate, 'TIME' AS mytype, NPAYCODE, cgroup2 as dept, cgroup3 as job, SUM(nreghr) AS reghrs, SUM(novert1) AS othrs, 0 as typecnt FROM TIME GROUP BY cempid, DWORKDATE, npaycode, cgroup2, CGROUP3 UNION SELECT cempid, dworkdate, 'DET' AS mytype, NPAYCODE, cgroup2 as dept, cgroup3 as job, SUM(nreghr) AS reghrs, SUM(novert1) AS othrs, 1 as typecnt FROM TIME_DET GROUP BY cempid, DWORKDATE, npaycode, cgroup2, CGROUP3 ) as A where mytype = 'DET' or (mytype = 'TIME' and exists (select CEMPID, DWORKDATE, SUM(typecnt) typecnt group by cempid, dworkdate having SUM(typecnt)= 0 )and cempid = a.cempid and dworkdate = a.dworkdate ) How can I get this to work?
selectmssqlsubquery
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Do you have any sample data that we could play with?
0 Likes 0 ·
smeachum avatar image
smeachum answered
Never mind. I figured it out. Here is what worked: SELECT cempid, dworkdate, 'DET' AS mytype, NPAYCODE, cgroup2 as dept, cgroup3 as job, SUM(nreghr) AS reghrs, SUM(novert1) AS othrs FROM TIME_DET WHERE DWORKDATE >= '01/01/2013' GROUP BY cempid, DWORKDATE, npaycode, cgroup2, CGROUP3 UNION SELECT t.cempid, t.dworkdate, 'TIME' AS mytype, t.NPAYCODE, t.cgroup2 as dept, t.cgroup3 as job, SUM(t.nreghr) AS reghrs, SUM(t.NOVERT1) AS othrs FROM TIME T where t.DWORKDATE >= '01/01/2013' and not exists ( select * from TIME_DET D where t.CEMPID = d.CEMPID and t.DWORKDATE = d.DWORKDATE ) group by t.CEMPID, t.dworkdate, t.NPAYCODE, t.CGROUP2, t.cgroup3
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

smeachum avatar image
smeachum answered
I'm not sure the best way to do this, but here are records for one employee for a week: cempid dworkdate mytype NPAYCODE dept job reghrs othrs typecnt 1126 03/10/2014 DET 32.00 91I 93 4.00 0.00 1 1126 03/10/2014 DET 32.00 92I 93 4.00 0.00 1 1126 03/10/2014 TIME 32.00 92I 93 8.00 0.00 0 1126 03/11/2014 DET 32.00 91I 93 4.00 0.00 1 1126 03/11/2014 DET 32.00 92I 93 4.00 0.00 1 1126 03/11/2014 TIME 32.00 92I 93 8.00 0.00 0 1126 03/12/2014 DET 32.00 91I 93 4.00 0.00 1 1126 03/12/2014 DET 32.00 92I 93 4.00 0.00 1 1126 03/12/2014 TIME 32.00 92I 93 8.00 0.00 0 1126 03/13/2014 DET 32.00 91I 93 4.00 0.00 1 1126 03/13/2014 DET 32.00 92I 93 4.00 0.00 1 1126 03/13/2014 TIME 32.00 92I 93 8.00 0.00 0 1126 03/14/2014 DET 32.00 91I 93 4.00 0.00 1 1126 03/14/2014 DET 32.00 92I 93 4.00 0.00 1 1126 03/14/2014 TIME 32.00 92I 93 8.00 0.00 0 Can you work with this?
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.