Kunal Batra Kunal Batra - 1 month ago 5
MySQL Question

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?

Answer

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.

Comments