piet piet - 11 months ago 48
MySQL Question

aggregation give result of 0 although no data exists

Having following sql with a join:

SELECT sum(r) AS amount
FROM rep.stats
JOIN rep.valdata
ON rep.valdata.id = rep.stats.data_id
WHERE rep.valdata.custid = 3657 AND
rep.stats.sdatetime LIKE '2014-09-29%'

Problem, my ResultSet gives me one row as result with the value of 0 although no data available. I expect no resultSet. Whats wrong? Is it, because sum(r) return a 0 as default?

This brings me in trouble cause also a 0 value is a value and will be displayed.

Answer Source

Your query is an aggregation query with no GROUP BY. It is an aggregation query because you have an aggregation function (SUM()) in the select clause.

An aggregation query with no group by treats all the rows as the group -- and all means all, including none. So, such a query always returns exactly one row which summarizes all rows.

That said, the query should not be returning a value of 0. It should be returning a single row with a value of NULL.

If you want no row, then add a group by:

SELECT sum(r) AS amount
FROM rep.stats s JOIN
     rep.valdata v
      ON v.id = s.data_id
WHERE v.custid = 3657 AND
      s.sdatetime >= '2014-09-29' AND
      s.sdatetime < '2015-09-30'
GROUP BY v.custid;


  • The aggregation query now has a GROUP BY. This will return one row per group, and if there are no groups, no rows will be returned.
  • Table aliases make the query easier to write and to read.
  • Don't use LIKE with date/time values. This requires converting the date/time to a string, which can depend on internationalization settings. Plus, it prevents the use of indexes.