How to query multiple SUMs of the same item using

问题:

• I'm creating a JasperReport using iReport, and as such, I'm limited* to one SQL query.

• I have a table 'statistics', with a 'name' (VARCHAR), 'count'(INTEGER), and 'datetime'(DATETIME) columns.

• 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)

Working SQL Statement:

SELECT
  SUM(count)as 'today'
FROM
  statistics
WHERE
   name = "test"
  AND $P{oneDayAgo} <= datetime
  AND datetime <= $P{now}

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

Failed SQL Statement:

SELECT
     SUM(count)as 'today'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneDayAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count)as 'thisWeek'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneWeekAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count)as 'thisMonth'
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneMonthAgo} <= datetime
 AND datetime <= $P{now}

(*) one can add additional queries only for graphs or cross-tabs, neither of which serve my purpose.

回答1:

sum(case when -condition- then count else 0 end)

SELECT
  SUM(case when $P{oneDayAgo} <= datetime then count else 0 end) as 'today',
  SUM(case when $P{oneWeekAgo} <= datetime then count else 0 end) as 'thisweek',
  SUM(count) as 'thismonth'
FROM
  statistics
WHERE
   name = "test"
  AND $P{oneMonthAgo} <= datetime
  AND datetime <= $P{now}

note that if you need averages, be sure to substitute NULL for 0.

回答2:

UNION'd queries should produce the same columns (name, type). Set unused columns to NULL or use a differenciation column:

SELECT
     SUM(count) as `total`,
     'today' as `when`
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneDayAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count) as `total`,
     'thisWeek' as `when`
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneWeekAgo} <= datetime
 AND datetime <= $P{now}
UNION
SELECT
     SUM(count) as `total`,
     'thisMonth' as `when`
FROM
     statistics
WHERE
     name = "test"
 AND $P{oneMonthAgo} <= datetime
 AND datetime <= $P{now}

回答3:

You have 2 options:

1) Remove the 'as' part of each query and then it will come as 1 column that won't have a name

2) Create a temp table and insert those rows into a temp table and then query the temp table