Group by date month multiple tables

3463 views mysql
9

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

answered question

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 ?

@MadhurBhaiya for only months that i have in my database

Your example code only uses two tables.

1 Answer

7

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 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.

posted this

Have an answer?

JD

Please login first before posting an answer.