SQL Query Aggregate Function

Question

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

Is this MySQL or SQL Server? Please choose one

DavidG
– DavidG

2015-07-09 08:23:54 +00:00
Commented Jul 9, 2015 at 8:23 — DavidG
– DavidG, Commented Jul 9, 2015 at 8:23
MySQL is the database

Hashir Rizwan
– Hashir Rizwan

2015-07-09 08:25:33 +00:00
Commented Jul 9, 2015 at 8:25 — Hashir Rizwan
– Hashir Rizwan, Commented Jul 9, 2015 at 8:25

Felix Pamittan · Accepted Answer · 2015-07-09 08:27:48Z

2

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.

answered Jul 9, 2015 at 8:27

Felix Pamittan

31.9k7 gold badges43 silver badges71 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

SQL Query Aggregate Function

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related