Mysql – Ordering a parent table by SUM() of colums of two of its child tables

join;MySQL

I know that title might sound confusing, but i will try to explain my problem as much as i can.

I have four tables:

companies{
id,
name,
address
}

stores{
id,
name,
address,
company_id
}

invoices{
id,
total,
date_time,
store_id
}

receipts{
id,
total,
date_time,
store_id
}

As you can see, store is linked to a company and both invoices and receipts are linked to a store.
I am having a problem writing a query which will return a list of stores by a company name, ordered by their turnover.

For example:

SELECT s.name as storename, SUM(i.total) as turnover
FROM companies c
  INNER JOIN stores s on s.company_id = c.id
  INNER JOIN invoices i ON i.store_id = s.id 
WHERE c.name = 'Com1' 
AND QUARTER(i.date_time) = 4
AND YEAR(i.date_time) = 2019
GROUP BY s.name
ORDER BY turnover DESC

I know how to order a store by a sum(invoices.total) or by sum(receipts.total), but i struggle to find a way to combine the two. I wish for end result to be something like this.

+-----------+----------+
| store_name| turnover |
+-----------+----------+
|    store1 | 1231.43  |
|    store2 | 2342.34  |
|    store3 | 4323.21  |
+-----------+----------+

Where turnover will be the SUM of SUM(invoices.total) and SUM(receipts.total)

Best Answer

SELECT s.name as storename, i.turnover + r.turnover as turnover
FROM companies c
INNER JOIN stores s on s.company_id = c.id
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM invoices
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) i ON i.store_id = s.id 
INNER JOIN ( SELECT store_id, SUM(total) as turnover
             FROM receipts
             WHERE QUARTER(date_time) = 4
               AND YEAR(date_time) = 2019
             GROUP BY store_id ) r ON r.store_id = s.id 
WHERE c.name = 'Com1' 
ORDER BY turnover DESC

?

Related Question