CODEWITHSUNDEEP
mysql
Kunal Batra
Kunal Batra

Reputation: 991

How to display 0 counts if value not exists in group by statement in mysql?

My query is working fine if i dnt use where condition

select a.mnth,count(b.month) from table1  b right join (SELECT 'January' mnth,'01' as set_or
            UNION ALL
            SELECT 'February' mnth,'02' as set_or
            UNION ALL
            SELECT 'March' mnth,'03' as set_or
            UNION ALL
            SELECT 'APRIL' mnth,'04' as set_or
            UNION ALL
            SELECT 'MAY' mnth,'05' as set_or
                        UNION ALL
            SELECT 'JUNE' mnth,'06' as set_or
                        UNION ALL
            SELECT 'JULY' mnth,'07' as set_or
                        UNION ALL
            SELECT 'AUGUST' mnth,'08' as set_or
                        UNION ALL
            SELECT 'SEPTEMBER' mnth,'09' as set_or
                        UNION ALL
            SELECT 'OCTOBER' mnth,'10' as set_or
            UNION ALL
            SELECT 'NOVEMBER' mnth,'11' as set_or
            UNION ALL
            SELECT 'DECEMBER' mnth,'12' as set_or
            ) a on a.mnth = b.month   group by a.mnth  order by a.set_or asc;



+-----------+----------------+
| mnth      | count(b.month) |
+-----------+----------------+
| January   |              0 |
| February  |              0 |
| March     |              2 |
| APRIL     |              1 |
| MAY       |              0 |
| JUNE      |              1 |
| JULY      |              1 |
| AUGUST    |              0 |
| SEPTEMBER |              0 |
| OCTOBER   |              0 |
| NOVEMBER  |              0 |
| DECEMBER  |              0 |
+-----------+----------------+

But i use where condion in the same query it dont display 0 against every month name

select a.mnth,count(b.month) from table1 b right join (SELECT 'January' mnth,'01' as set_or
            UNION ALL
            SELECT 'February' mnth,'02' as set_or
            UNION ALL
            SELECT 'March' mnth,'03' as set_or
            UNION ALL
            SELECT 'APRIL' mnth,'04' as set_or
            UNION ALL
            SELECT 'MAY' mnth,'05' as set_or
                        UNION ALL
            SELECT 'JUNE' mnth,'06' as set_or
                        UNION ALL
            SELECT 'JULY' mnth,'07' as set_or
                        UNION ALL
            SELECT 'AUGUST' mnth,'08' as set_or
                        UNION ALL
            SELECT 'SEPTEMBER' mnth,'09' as set_or
                        UNION ALL
            SELECT 'OCTOBER' mnth,'10' as set_or
            UNION ALL
            SELECT 'NOVEMBER' mnth,'11' as set_or
            UNION ALL
            SELECT 'DECEMBER' mnth,'12' as set_or
            ) a on a.mnth = b.month  where b.year like '2016' group by a.mnth  order by a.set_or asc;


+-------+----------------+
| mnth  | count(b.month) |
+-------+----------------+
| March |              2 |
| APRIL |              1 |
| JUNE  |              1 |
| JULY  |              1 |
+-------+----------------+

Here is my table description

table1;

+-------------+---------------+------+-----+-------------------+-----------------------------+
| Field       | Type          | Null | Key | Default           | Extra                       |
+-------------+---------------+------+-----+-------------------+-----------------------------+
| month       | varchar(100)  | NO   |     | NULL              |                             |
| Year        | int(100)      | NO   |     | NULL              |                             |
+-------------+---------------+------+-----+-------------------+------------------------------

can anyone please tell me how can i get 0 counts is any months does not exists?

Upvotes: 1

Views: 77

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17137

I have rearranged order of your tables (appearance in code) and changed RIGHT JOIN to LEFT JOIN respectively. Put your WHERE clause constraint into the JOIN so that no INNER JOIN is implied:

select 
  a.mnth,
  count(b.month)
from (
  SELECT 'January' mnth,'01' as set_or
  UNION ALL
  SELECT 'February' mnth,'02' as set_or
  UNION ALL
  SELECT 'March' mnth,'03' as set_or
  UNION ALL
  SELECT 'APRIL' mnth,'04' as set_or
  UNION ALL
  SELECT 'MAY' mnth,'05' as set_or
  UNION ALL
  SELECT 'JUNE' mnth,'06' as set_or
  UNION ALL
  SELECT 'JULY' mnth,'07' as set_or
  UNION ALL
  SELECT 'AUGUST' mnth,'08' as set_or
  UNION ALL
  SELECT 'SEPTEMBER' mnth,'09' as set_or
  UNION ALL
  SELECT 'OCTOBER' mnth,'10' as set_or
  UNION ALL
  SELECT 'NOVEMBER' mnth,'11' as set_or
  UNION ALL
  SELECT 'DECEMBER' mnth,'12' as set_or
  ) a 
left join donation_entry b on 
  a.mnth = b.month 
  and b.year = '2016' -- here is the change
group by a.mnth
order by a.set_or asc;

Note that I've also replaced like with equality operator = since without wildcards for partial matching it is essentially the same and avoids confusion.

Upvotes: 2

Related Questions