Having following sql with a join:
SELECT sum(r) AS amount
ON rep.valdata.id = rep.stats.data_id
WHERE rep.valdata.custid = 3657 AND
rep.stats.sdatetime LIKE '2014-09-29%'
Your query is an aggregation query with no
GROUP BY. It is an aggregation query because you have an aggregation function (
SUM()) in the
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
If you want no row, then add a
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;
GROUP BY. This will return one row per group, and if there are no groups, no rows will be returned.
LIKEwith 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.