group by query challenge

Question

Hi,

I have these 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 figure out a way to get both in a single query. 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 a single (MySql) query?

Cheers, Don

Answer 1

How about something like

select c.id, count(o.order_id),sum(if(o.discount,1,0))
from customer c 
left join order as o using c.customer_id = o.customer_id
group by c.id

Answer 2

You could do somethings like

select 
 c.id,
 sum(case o.discount when true then 1 else 0 end) as 'total discounted',
 count(o.order_id) as 'total orders'
from customer as c
 left join order as o using c.customer_id = o.customer_id 
group by c.id

Answer 3

Other answers get close, but here's how I'd 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 the usage of USING requires parentheses, and it only accepts a list of columns that will be compared with =. You can't give a full comparison expression with the USING syntax as you can with the ON syntax.

Also you can simplify the expression inside SUM() because an equality comparison returns either 1 or 0.

See also "Query: count multiple aggregates per item"