使用不同条件的同一个表中的一个 SQL 查询中的多个聚合函数

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.

我正在创建一个 SQL 查询，该查询将根据两个聚合函数的值从表中提取记录。这些聚合函数从同一个表中提取数据，但具有不同的过滤条件。我遇到的问题是 SUM 的结果比我只包含一个 SUM 函数的结果要大得多。我知道我可以使用临时表创建这个查询，但我只是想知道是否有一个只需要一个查询的优雅解决方案。

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.

任何见解将不胜感激。

回答by Jeremy

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.

这样，如果您更改案例条件，它只会出现在一个位置。

回答by Tomalak

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.

此外，如果只有 SQL 关键字是大写而其余的不是，可读性会增加。恕我直言。

回答by Lukas Eder

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

值得注意的是，您实际上不必为表达式中0的ELSEcase提供值，CASE如Jeremy's answer所示。由于聚合函数不聚合NULL值，您可以将其留空并CASE仅使用表达式来指定要包含在聚合中的值，例如

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 ...

回答by HLGEM

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