Group on request request
I have the following tables:
customer
--------
customer_id int
name varchar(255)
order
-----
order_id int
customer_id int
discount boolean
I can get the number of orders made by each customer with a query like:
select c.id, count(o.order_id)
from customer c
left join order as o using c.customer_id = o.customer_id
group by 1
Alternatively, I can get the number of discounted orders made by each customer with:
select c.id, count(o.order_id)
from customer c
left join order as o using c.customer_id = o.customer_id and o.discount = true
group by 1
But I can't seem to find a way to get both in one request. I've tried the following:
select c.id, count(o.order_id), count(o2.order_id)
from customer c
left join order as o using c.customer_id = o.customer_id
left join order as o2 using c.customer_id = o2.customer_id and o2.discount = true
group by 1
But it didn't work. Is it possible to calculate both in one (MySql) query?
Cheers, Don
source to share
Other answers are close, but here's how I would write it:
SELECT c.id, COUNT(o.order_id) AS order_count,
SUM(o.discount = true) AS discount_order_count
FROM customer c
LEFT OUTER JOIN order AS o USING (customer_id)
GROUP BY c.id;
Note that usage USING
requires parentheses and only accepts a list of columns to be compared against =
. You cannot give a complete comparison expression with syntax USING
as you can with syntax ON
.
Also you can simplify the expression internally SUM()
because equality comparison returns either 1 or 0.
source to share