如何使用SQL在iReport的查询同一项目的多个和(How to query multiple S

我创建使用iReport的一个JasperReport的，正因为如此，我有限公司*到一个SQL查询。
我有一个表 '统计'，以 '名'（VARCHAR）， '计数'（整数），和 '日期时间'（DATETIME）列。
这是很简单的得到“计数”列的总和时，“名称”是“测试”的最后一天，同样的最后一个星期和月（参见下文）

工作SQL语句：


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

然而，由于我只有一个SQL语句的工作，我需要以某种方式将它们结合起来。我试着使用UNION（作为波纹管），但并没有工作。

失败的SQL语句：

 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}

（*）一个只能用于图表或交叉表，两者都不满足我的目的添加额外的查询。

Answer 1:

总和（情况下，当-condition-然后计数否则为0端）

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}

请注意，如果你需要平均值，一定要替换为NULL 0。

Answer 2:

UNION'd查询应该产生相同的列（名称，类型）。设置使用的列设置为NULL或使用differenciation列：

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}