CODEWITHSUNDEEP
mysqlsql
mtmacdonald
mtmacdonald

Reputation: 15080

Why does this group by statement return no rows?

My readings table schema looks like this:

id | date | item_id | module_id | reading

My basic query is working and returns always one row (with a value if there are readings between the specified dates, and with null if there aren't).

select SUM(`reading`) as `reading` from `readings` 
where `item_id`=1 
and `date` between '2014-02-01' and '2014-02-20';

But now I want to add a group by statement like this:

select SUM(`reading`) as `reading` from `readings` 
where `item_id`=1 
and `date` between '2014-02-01' and '2014-02-20'
group by `module_id`;

It now returns one row only when there are readings between the specified dates. If there are no readings between the specified dates, it returns zero rows.

My question is why does adding this group by statement return no rows? How do I make it behave consistently?

Upvotes: 1

Views: 2005

Answers (2)

Gabber
Gabber

Reputation: 7249

its working for me

select SUM(m.`reading`) as `reading` from `readings` as m,`readings` as n
where m.item_id=1 
and m.`date` between '2014-02-01' and '2014-02-20'
group by n.`module_id`;

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269973

When you don't have a group by statement, the SQL definition is to create one group for the results. This is true even when no rows match.

When you have a group by statement, then each group is created. If there are no rows, then no groups are created.

In some ways, this behavior is inconsistent, but it is how SQL works. If you want to get more rows, you can re-write the query using conditional aggregation:

select module_id,
       SUM(case when `item_id`= 1 and `date` between '2014-02-01' and '2014-02-20' then `reading`
           end) as `reading`
from `readings` 
group by `module_id`;

This will return a row for each module_id. If no rows match the conditions, then the value of reading will be NULL. You can make it 0 instead by adding else 0 to the case statement.

Upvotes: 2

Related Questions