Select month, year and quantity and show months with zero entries

Question

I have the following mysql query:

SELECT 
LEFT(MONTHNAME(date), 3) AS month,
YEAR(date) AS year,
COUNT(id) AS quantity
FROM
budgets
GROUP BY MONTH(date)

which returns me this:

|month|year|quantity|
---------------------
|  Jan|2018|      10|
|  Mar|2018|       2|
|  May|2018|       5|

I need to show months even if COUNT(id) not exist like this:

|month|year|quantity|
---------------------
|  Jan|2018|      10|
|  Feb|2018|       0|
|  Mar|2018|       2|
|  Apr|2018|       0|
|  May|2018|       5|

Anyone knows a way to do that?

UPDATE

According to D-Shih answer code, query returns this:

|month|year|quantity|
---------------------
|  Jan|2018|      10|
|  Feb|null|       0|
|  Mar|2018|       2|
|  Apr|null|       0|
|  May|2018|       5|

And if I add a register with different year it will show only one row with name 2017 like:

|month|year|quantity|
---------------------
|  Jan|2018|      10|
|  Feb|null|       0|
|  Mar|2018|       2|
|  Apr|null|       0|
|  May|2018|       5|
|  May|2017|       2|

it must show

|month|year|quantity|
---------------------
|  Jan|2018|      10|
|  Feb|2018|       0|
|  Mar|2018|       2|
|  Apr|2018|       0|
|  May|2018|       5|
|  Jan|2017|       0|
|  Feb|2017|       0|
|  Mar|2017|       0|
|  Apr|2017|       0|
|  May|2017|       2|

Vishnu Kunchur · Answer 1 · 2018-09-07T20:03:48.113

0

If you want to scale this, I think this should work. Not very sure about how efficient it would be though:

SELECT a.month AS month, a.year AS year, COALESCE(b.quantity,0) AS quantity
FROM
(
   SELECT DISTINCT MONTH(date) AS month, YEAR(date) AS year
   FROM
   budgets
) a
LEFT JOIN
(
   SELECT MONTH(date) AS month, YEAR(date) AS year,
   COUNT(id) AS quantity
   FROM
   budgets
   GROUP BY MONTH(date), YEAR(date)
) b
ON a.month = b.month AND a.year = b.year

edited Sep 07 '18 at 20:03

answered Sep 07 '18 at 19:53

Vishnu Kunchur

1,716
8
9

Hi Vishnu, the code returns same values from normal select! – Alexandre Hansen Sep 07 '18 at 21:25

D-Shih · Accepted Answer · 2018-09-07T21:37:11.633

0

You can try to make a calendar table and OUTER JOIN base on the calendar table

SELECT t1.month,
       y.year AS year,
       COUNT(t2.id) 
FROM (
    SELECT 'Jan' month
    UNION ALL
    SELECT 'Feb' month
    UNION ALL
    SELECT 'Mar' month
    UNION ALL
    SELECT 'Apr' month
    UNION ALL
    SELECT 'May' month
) t1 
CROSS JOIN (SELECT DISTINCT YEAR(date) FROM budgets) y  
LEFT JOIN budgets t2 on t1.month = LEFT(MONTHNAME(t2.date), 3) 
   AND y.year = YEAR(t2.date)
GROUP BY t1.month, y.year

Here is a sample for you.

Schema (MySQL v8.0)

CREATE TABLE budgets (
   `month` varchar(5),
   `year` int,
   quantity int
);


insert into budgets values ('Jan',2018,10);
insert into budgets values ('Mar',2018,2);
insert into budgets values ('May',2018,5);
insert into budgets values ('May',2017,2);

Query #1

| month | year | quantity |
| ----- | ---- | -------- |
| Jan   | 2018 | 10       |
| Mar   | 2018 | 2        |
| May   | 2018 | 5        |
| May   | 2017 | 2        |
| Jan   | 2017 | 0        |
| Feb   | 2018 | 0        |
| Feb   | 2017 | 0        |
| Mar   | 2017 | 0        |
| Apr   | 2018 | 0        |
| Apr   | 2017 | 0        |

View on DB Fiddle

edited Sep 07 '18 at 21:37

answered Sep 07 '18 at 19:55

D-Shih

44,943
6
31
51

Ok, it worked, but if I had a value that is another year it also counts. There is a possibility count different years? – Alexandre Hansen Sep 07 '18 at 20:18
What's your mysql version? – D-Shih Sep 07 '18 at 20:28
it's 5.7.14, I`ll post edit answer with screen what is going on with – Alexandre Hansen Sep 07 '18 at 20:32
I`ve updated the question. Can you analize it? – Alexandre Hansen Sep 07 '18 at 21:25
@AlexandreHansen OK wait a secnod. – D-Shih Sep 07 '18 at 21:34
@AlexandreHansen You can try it. – D-Shih Sep 07 '18 at 21:37
it worked. Thank you very much sir!!! – Alexandre Hansen Sep 08 '18 at 00:28

Select month, year and quantity and show months with zero entries

2 Answers2