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?
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
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?
15 People are following this question.
