MySQL query with zero values

Question

I have these two tables (Article and Sale):

id  |  name     |
====+============
1   |   milk    |
2   |   apple   |
3   |   bread   |
... |    ...    |


id  |   idArticle  |   date     |
====+==============+=============
1   |       2      | 2011-01-01 |
2   |       2      | 2011-01-01 |
3   |       3      | 2011-01-01 |
4   |       1      | 2011-01-02 |
... |      ...     |    ...     |

I need to get the sales for 2011/01/01: article and count

2011-01-01 Milk 0
2011-01-01 Apple 2
2011-01-01 Bread 1
...

But I don't know how to show "Milk: 0" because it didn't sell milk that day.

This query doesn't work:

SELECT s.date, a.name, COUNT(*)
FROM article a
LEFT JOIN sale s ON a.id = s.idArticle
WHERE s.date = "2011-01-01"
GROUP BY s.date, a.name

read this. dev.mysql.com/doc/refman/5.0/en/join.html Left join is your mistake here. Try inner join — Ron van der Heijden
– Ron van der Heijden, Commented Jun 5, 2012 at 9:13
@Bondye: but he wants it that way, look at the desired output (milk)... — Nikola Bogdanović
– Nikola Bogdanović, Commented Jun 5, 2012 at 9:31
@pOcHa But I don't know how to show "Milk: 0" because it didn't sell milk that day. Have you realy read this question? — Ron van der Heijden
– Ron van der Heijden, Commented Jun 5, 2012 at 10:08
@Bondye: my answer already takes care of all that, it displays the output exactly as desired, and is optimized for speed too (grouping before joining) - his query shows milk (as intended) but no date and displays 1 instead of 0 (and with inner join there would not be milk at all, do you get it now?) — Nikola Bogdanović
– Nikola Bogdanović, Commented Jun 5, 2012 at 10:14

Nikola Bogdanović · Accepted Answer · 2012-06-05 09:09:03Z

2

SELECT "2011-01-01" AS date, a.name, IFNULL(s.total, 0)
FROM article a
LEFT JOIN (
    SELECT idArticle, COUNT(*) AS total
    FROM sale
    WHERE date = "2011-01-01"
    GROUP BY idArticle
) AS s ON a.id = s.idArticle

answered Jun 5, 2012 at 9:09

Nikola Bogdanović

3,2231 gold badge22 silver badges28 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nikola Bogdanović Over a year ago

just change "2011-01-01" to a @date parameter, and it will work for any day - and if you want it for a date range, then change WHERE date = "2011-01-01" to WHERE date BETWEEN @dateMin AND @dateMax, but remove date column from select...

xdazz · Accepted Answer · 2012-06-05 09:24:53Z

0

SELECT a.name, COUNT(s.id)
FROM article a
LEFT JOIN sale s ON a.id = s.idArticle AND s.date = '2011-01-01'
GROUP BY a.name

edited Jun 5, 2012 at 9:24

answered Jun 5, 2012 at 9:06

xdazz

161k38 gold badges255 silver badges278 bronze badges

1 Comment

Nikola Bogdanović Over a year ago

what exactly are you counting without the GROUP BY a.name

Collectives™ on Stack Overflow

MySQL query with zero values

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related