Multiple aggregate functions in one SQL query from the same table using different conditions
Solution 1
SELECT
E.EMPID
,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE ON
ABSENCE.EMPID = E.EMPID
GROUP BY
E.EMPID
HAVING
SUM(ATOTAL.HOURS_ABSENT) > 10
OR SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) > 3
edit:
It's not a big deal, but I hate repeating conditions so we could refactor like:
Select * From
(
SELECT
E.EMPID
,SUM(ABSENCE.HOURS_ABSENT) AS ABSENT_TOTAL
,SUM(case when year(Date) = 2010 then ABSENCE.HOURS_ABSENT else 0 end) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE ON
ABSENCE.EMPID = E.EMPID
GROUP BY
E.EMPID
) EmployeeAbsences
Where ABSENT_TOTAL > 10 or ABSENT_YEAR > 3
This way, if you change your case condition, it's in one spot only.
Solution 2
Group different things separately, join groups.
SELECT
T.EMPID
,T.ABSENT_TOTAL
,Y.ABSENT_YEAR
FROM
(
SELECT
E.EMPID
,SUM(A.HOURS_ABSENT) AS ABSENT_TOTAL
FROM
EMPLOYEE E
INNER JOIN ABSENCE A ON A.EMPID = E.EMPID
GROUP BY
E.EMPID
) AS T
INNER JOIN
(
SELECT
E.EMPID
,SUM(A.HOURS_ABSENT) AS ABSENT_YEAR
FROM
EMPLOYEE E
INNER JOIN ABSENCE A ON A.EMPID = E.EMPID
WHERE
A.DATE > '1/1/2010'
GROUP BY
E.EMPID
) AS Y
ON T.EMPLID = Y.EMPLID
WHERE
ABSENT_TOTAL > 10 OR ABSENT_YEAR > 3
Also, if only SQL keywords are caps and the rest is not, readability increases. IMHO.
Eric Ness
Updated on July 09, 2022Comments
-
Eric Ness almost 2 years
I'm working on creating a SQL query that will pull records from a table based on the value of two aggregate functions. These aggregate functions are pulling data from the same table, but with different filter conditions. The problem that I run into is that the results of the SUMs are much larger than if I only include one SUM function. I know that I can create this query using temp tables, but I'm just wondering if there is an elegant solution that requires only a single query.
I've created a simplified version to demonstrate the issue. Here are the table structures:
EMPLOYEE TABLE EMPID 1 2 3 ABSENCE TABLE EMPID DATE HOURS_ABSENT 1 6/1/2009 3 1 9/1/2009 1 2 3/1/2010 2
And here is the query:
SELECT E.EMPID ,SUM(ATOTAL.HOURS_ABSENT) AS ABSENT_TOTAL ,SUM(AYEAR.HOURS_ABSENT) AS ABSENT_YEAR FROM EMPLOYEE E INNER JOIN ABSENCE ATOTAL ON ATOTAL.EMPID = E.EMPID INNER JOIN ABSENCE AYEAR ON AYEAR.EMPID = E.EMPID WHERE AYEAR.DATE > '1/1/2010' GROUP BY E.EMPID HAVING SUM(ATOTAL.HOURS_ABSENT) > 10 OR SUM(AYEAR.HOURS_ABSENT) > 3
Any insight would be greatly appreciated.
-
user979051 over 11 yearsThank you so much! You've introduced me to "case when"...you got me out of big problem I've been having!