title: How to query multiple SUMs of the same item using SQL in iReport
body: <ul>
  <li><p>I'm creating a JasperReport using iReport, and as such, I'm limited* to one SQL query.</p></li>
  <li><p>I have a table 'statistics', with a 'name' (VARCHAR), 'count'(INTEGER), and 'datetime'(DATETIME) columns.</p></li>
  <li><p>It is simple enough to get the sum of the 'count' column when the 'name' was "test" for the last day, and similarly for the last week, and month (see bellow)</p></li>
  </ul>
  
  <p>Working SQL Statement:</p>
  
  <pre><code>
  SELECT
    SUM(count)as 'today'
  FROM
    statistics
  WHERE
     name = "test"
    AND $P{oneDayAgo} &lt;= datetime
    AND datetime &lt;= $P{now}
  </code></pre>
  
  <ul>
  <li>However, since I only have one SQL statement to work with, I need to somehow combine them. I tried using UNION (as bellow) but this didn't work. </li>
  </ul>
  
  <p>Failed SQL Statement:
  <code><pre>
  SELECT
       SUM(count)as 'today'
  FROM
       statistics
  WHERE
       name = "test"
   AND $P{oneDayAgo} &lt;= datetime
   AND datetime &lt;= $P{now}
  UNION
  SELECT
       SUM(count)as 'thisWeek'
  FROM
       statistics
  WHERE
       name = "test"
   AND $P{oneWeekAgo} &lt;= datetime
   AND datetime &lt;= $P{now}
  UNION
  SELECT
       SUM(count)as 'thisMonth'
  FROM
       statistics
  WHERE
       name = "test"
   AND $P{oneMonthAgo} &lt;= datetime
   AND datetime &lt;= $P{now}
  </code></pre></p>
  
  <p>(*) one can add additional queries only for graphs or cross-tabs, neither of which serve my purpose.</p>