How to write a subquery in a grouping query?

Question

MySQL

Friends, tell me please, I can’t figure out how to write the following query:

There is a table: sales

id INT
total_sum decimal
type varchar
event_date timestamp
...

And table: sale_items

sale_id int
quantity decimal
...

I need to get information about sales grouped by event_date , but I can't figure out how to calculate the sum of all sale_items in a subquery. Here's the current query (with a commented-out subquery that makes the whole query invalid):

select
       DATE(event_date),
       COUNT(*)                                sales_count,
       COUNT(IF(type = 'refund', 1, NULL))     refunds_count,
       SUM(IF(type = 'sale', total_sum, 0))    revenue,
       AVG(IF(type = 'sale', total_sum, NULL)) avg_check,
#        (select count(*) from sale_items where sale_id=sales.id) as sale_items_count
from sales
group by DATE(event_date);

Is it possible to do this with a single request? I would be very grateful for your help with the request.

Answer 1

something like this

...
  sum(i.count)
from sales
join (
  select sale_id, count(*) count from sale_items
) i on (i.sale_id = sales.id)
group by DATE(event_date);