CODEWITHSUNDEEP
sqlsql-servert-sqlaggregate-functions
Eric Ness
Eric Ness

Reputation: 10417

Multiple aggregate functions in one SQL query from the same table using different conditions

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.

Upvotes: 19

Views: 122288

Answers (4)

Lukas Eder
Lukas Eder

Reputation: 220797

It's worth noticing that you don't actually have to provide a 0 value for the ELSE case in your CASE expression as shown in Jeremy's answer. Because aggregate functions do not aggregate NULL values, you can just leave that empty and use the CASE expression only to specify what values to include in your aggregation, e.g.

SELECT 
  e.empid,
  SUM(CASE WHEN <something> THEN atotal.hours_absent END) AS absent_total,
  SUM(CASE WHEN <something> THEN ayear.hours_absent END) AS absent_year
FROM ...

Upvotes: 1

Tomalak
Tomalak

Reputation: 338158

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.

Upvotes: 6

HLGEM
HLGEM

Reputation: 96552

SELECT E.EMPID , sum(ABSENT_TOTAL) , sum(ABSENT_YEAR)
FROM
(SELECT 
    E.EMPID 
    ,SUM(ATOTAL.HOURS_ABSENT) AS ABSENT_TOTAL 
    ,0 AS ABSENT_YEAR 

FROM 
    EMPLOYEE E 

    INNER JOIN ABSENCE ATOTAL ON 
        ATOTAL.EMPID = E.EMPID 
WHERE 
    AYEAR.DATE > '1/1/2010' 

GROUP BY 
    E.EMPID 

HAVING 
    SUM(ATOTAL.HOURS_ABSENT) > 10 

 UNION ALL

 SELECT 0    
    ,SUM(AYEAR.HOURS_ABSENT) 
 FROM 
    EMPLOYEE E    

       INNER JOIN ABSENCE AYEAR ON 
        AYEAR.EMPID = E.EMPID 

GROUP BY 
    E.EMPID 

HAVING  
    SUM(AYEAR.HOURS_ABSENT) > 3) a

    GROUP BY A.EMPID

Upvotes: 0

Jeremy
Jeremy

Reputation: 4838

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.

Upvotes: 29

Related Questions