How can I create a aggregate data from 3 different tables in just 1 table?

Rodrigo Moro 0
Viewed 57

I hope you all are doing great! :D

I need your help to get the following done:

I need to create the following table:

Date Revenue gained from new deals Revenue lost from churn Revenue gained from upsell
01/jan/2022 $1000 -$500 $1000
02/jan/2022 $2000 -$200 $2000

The situation here is that to gather and aggregate this data I need to fetch 3 different tables:

deals, churns and upsells

The deals table:

Deal Closing date Revenue won
Deal #1 01/jan/2022 $500
Deal #2 01/jan/2022 $500
Deal #3 02/jan/2022 $1500
Deal #4 02/jan/2022 $500

The churns table:

Churn Closing date Revenue lost
Churn #1 01/jan/2022 -$500
Churn #2 02/jan/2022 -$100
Churn #3 02/jan/2022 -$100

The upsells table:

Upsell Closing date Revenue won
Upsell #1 01/jan/2022 $2000
Upsell #2 01/jan/2022 -$1000
Upsell #3 02/jan/2022 $2000

The first question is: How can I create a SQL command to get this done?

Thanks in advance.

mysql sql
2 Answers

You could use the subquery doing aggregation for churns and upsells tables.

Something like below:

select d.Closing_date,
       sum(d.Revenue_won) as 'Revenue gained from new deals', 
       c.`Revenue lost from churn`,
       u.`Revenue gained from upsell`
from deals d
inner join ( select Closing_date,
                    sum(Revenue_lost) as 'Revenue lost from churn'
            from churns 
            group by Closing_date 
            ) as c on c.Closing_date=d.Closing_date
inner join ( select Closing_date,
                    sum(Revenue_won) as 'Revenue gained from upsell'
             from upsells u 
             group by Closing_date 
             ) as u on  u.Closing_date=d.Closing_date          
group by d.Closing_date ;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0875563c9ab7f9385711dde21cd98b47

Please do not store date as text, you will face many difficulties. If you want to format the date you could use DATE_FORMAT

Note. If some other date exists on deals table and not on the two other table it will be filtered out from the results. If you want it use LEFT JOIN rather than INNER JOIN.

**Edit

select d.Closing_date,
       d.`Revenue gained from new deals`,
       c.`Revenue lost from churn`,
       u.`Revenue gained from upsell`
from  ( select Closing_date,
               sum(Revenue_won) as 'Revenue gained from new deals'
        from deals 
        group by Closing_date ) as d 
inner join ( select Closing_date,
                    sum(Revenue_lost) as 'Revenue lost from churn'
            from churns 
            group by Closing_date 
            ) as c on c.Closing_date=d.Closing_date
inner join ( select Closing_date,
                    sum(Revenue_won) as 'Revenue gained from upsell'
             from upsells u 
             group by Closing_date 
             ) as u on  u.Closing_date=d.Closing_date           ;

https://dbfiddle.uk/S61QeLBX

Ergest Basha0

since the tables aren't linked just run 3 queries and do the post-processing in the backend

Alex Skotner0
Related