title: Multiple aggregate functions in one SQL query from the same table using different conditions
body: <p>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.</p>
  
  <p>I've created a simplified version to demonstrate the issue.  Here are the table structures:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>And here is the query:</p>
  
  <pre><code>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 &gt; '1/1/2010'
  
  GROUP BY
      E.EMPID
  
  HAVING
      SUM(ATOTAL.HOURS_ABSENT) &gt; 10
      OR SUM(AYEAR.HOURS_ABSENT) &gt; 3
  </code></pre>
  
  <p>Any insight would be greatly appreciated.</p>