Multiple aggregate functions in one SQL query from the same table using different conditions
- by Eric Ness
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.
