CODEWITHSUNDEEP
mysqlsql
Parag Kadam
Parag Kadam

Reputation: 3850

Selecting row even when count value is 0 mysql

select c.id, count(*) as 'Transactions' from voucher v 
right join consumer c on v.consumer_id = c.id
where v.voucher_state in ('REDEEMED', 'EXPIRED') and c.country = 'INDIA'
group by c.id;

Expected output :-

Id     Transactions

3     0

4     0

6     3

7     9

8     4

9     0

Current output :-

Id     Transactions

6     3

7     9

8     4

How do I select the rows who has count = 0? Thanks.

Upvotes: 0

Views: 45

Answers (3)

John Woo
John Woo

Reputation: 263713

Since you are using RIGHT JOIN, the filtering condition on the left table must be in the ON clause. The reason is, the filtering will happen before the optimizer join it with the right table. The WHERE clause will filter in the final result.

Another thing, you must count only the rows in the left table (eg. COUNT(v.consumer_id))) rather than COUNT(*), otherwise, there will always be one count for each c.id.

select c.id, count(v.consumer_id) as 'Transactions' 
from voucher v 
     right join consumer c 
       on v.consumer_id = c.id 
          and v.voucher_state in ('REDEEMED', 'EXPIRED')
where  c.country = 'INDIA'
group  by c.id;

Upvotes: 1

SF..MJ
SF..MJ

Reputation: 880

use IFNULL

select c.id,IFNULL(COUNT(*), 0) as 'Transactions' from voucher v 
right join consumer c on v.consumer_id = c.id
where v.voucher_state in ('REDEEMED', 'EXPIRED') and c.country = 'INDIA'
group by c.id;

Upvotes: 0

Giorgos Betsos
Giorgos Betsos

Reputation: 72165

You have to use a LEFT JOIN, place consumer table on the left side and move 

 v.voucher_state in ('REDEEMED', 'EXPIRED'):

to ON clause:

select c.id, count(v.consumer_id) as 'Transactions' 
from consumer c
left join voucher v  on v.consumer_id = c.id on v.voucher_state in ('REDEEMED', 'EXPIRED')
where c.country = 'INDIA'
group by c.id;

The above query will return all customer.id values satisfying condition 

customer.country = 'INDIA'

Only customers having a matching record in voucher table with 

voucher.voucher_state in ('REDEEMED', 'EXPIRED')

will be counted.

Upvotes: 2

Related Questions