Group by date month multiple tables

Question

So I have 3 tables called :
Spending - to record expenses
Directv - to record subscription payments to TV channels
Sales - to record sales
and I have to calculate the turnover ,total amount of sales , total expenses and Directv for each month

    Spending
    id|Amount|dateSpd
    1 |2000  |2018-10-05
    3 |3000  |2018-11-06

    Directv
    id|Amount|dateTv
    1 |50    |2018-10-05

    Sales
    id|customer|quantity|price|dateSales
    1 |Marc    |2       |500  |2018-10-05
    2 |Kevin   |3       |1500 |2018-10-05
    5 |Angel   |2       |500  |2018-11-07

and I wish to get for example

turnover | Spending | sales | DirecTv | month | year
  5500   | 3000     | 2000  | 50      | 10     |2018
   1000  | 2000     | 500   | 0       | 11     |2018

i have some problems to get it , my queries :

--to get total amount of sales
select sum(sl.price) , month(sl.date) , year(sl.date) from sales sl GROUP by year(sl.date) , month(sl.date)

--for directtv
select sum(dv.amount) , month(dv.date) , year(dv.date) from directtv dv GROUP by year(dv.date) , month(dv.date)

--for turnover
SELECT sum(sl.quantity*sl.price) , month(sl.date) FROM sales sl GROUP by year(sl.date), month(sl.date)

but how how to group by date all SQL queries with joins
someone could help me or give me any hints ? thank you in advance

Do you want to get all the months, irrespective of whether there is any entry in either of the 3 tables ? So there can be a case when there is no turnover in let's say, August 2018, so you still want to get the row for August 2018, with all other values as 0 ? — Madhur Bhaiya, Nov 04 '18 at 11:54

Gordon Linoff · Accepted Answer · 2018-11-04T12:25:13.893

1

You can combine the data from the tables using union all and then aggregate.

I suspect you want something like this:

select year(dte), month(dte),
       sum(spending) as spending, sum(directtv) as directtv,
       sum(price*quantity) as turnover
from ((select datesp as dte, amount as spending, 0 as directtv, 0 as price, 0 as quantity
       from spending
      ) union all
      (select datetv as dte, 0 as spending, amount as directtv, 0 as price, 0 as quantity
       from directtv
      ) union all
      (select datesales as dte, 0 as spending, 0 as directtv, price, quantity
       from sales
      )
     ) x
group by year(dte), month(dte) ;

This is not exactly what is in your queries, but it makes sense given the data you have provided.

edited Nov 04 '18 at 12:25

answered Nov 04 '18 at 11:59

Gordon Linoff

1,242,037
58
646
786

there is a way to add a INNER JOIN ? – kevin73 Nov 04 '18 at 15:12
@kevin73 . . . You can add an inner join on any of the four `FROM` clauses. An inner join is not needed to answer this question. If you have another question, then ask it as *another* question. – Gordon Linoff Nov 04 '18 at 16:25
ok please check this question https://stackoverflow.com/questions/53150712/mysql-get-the-turnover-income-total-expenses-from-my-database-and-group-them/53150839#53150839 – kevin73 Nov 05 '18 at 09:00

Group by date month multiple tables

1 Answers1

Linked