CODEWITHSUNDEEP
mysql
Hashir Rizwan
Hashir Rizwan

Reputation: 3

SQL Query Aggregate Function

This is my query:

select a.id, a.title,count(x.id) as Orders
-- from x_vendordeliveriesareas vda
-- left join x_vendordeliveries vd
-- on vd.id = vda.vendordelivery_id
from x_orders x
left join x_areas a
on x.delivery_area_id  = a.id
-- on vda.area_id = a.id
-- left join x_orders x
left join x_vendors v
on v.id = x.vendor_id
where v.title like 'golden dragon%' and (date_format(x.date,'%Y-%m-%d') BETWEEN '2015-01-01' AND '2015-06-30') 
and x.status_id=11 
and x.country_id =11
and v.city_id=3
group by 1;

This works perfectly fine, but I want to return those areas to which have 0 orders. I have tried IFNULL and coalesce functions

Upvotes: 0

Views: 36

Answers (1)

Felix Pamittan
Felix Pamittan

Reputation: 31889

Your driving table should be x_areas:

select
    a.id, 
    a.title, 
    coalesce(count(x.id), 0) as Orders
from x_areas a
left join x_orders x
    on x.delivery_area_id = a.id
    and x.status_id = 11 
    and x.country_id = 11
    and (date_format(x.date,'%Y-%m-%d') BETWEEN '2015-01-01' AND '2015-06-30')
left join x_vendors v
    on v.id = x.vendor_id
where 
    v.title like 'golden dragon%'   
    and v.city_id = 3
group by 1;

Note that I moved some of your WHERE conditions in the ON clause to prevent the LEFT JOIN from turning into an INNER JOIN, thus giving the same result, with the added x_areas with 0 Orders.

Upvotes: 2

Related Questions