How to query multiple SUMs of the same item using SQL in iReport

Answer 1

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}

Answer 2

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.

Answer 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