I hope you all are doing great! :D

I need your help to get the following done:

I need to create the following table:

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:

The churns table:

The upsells table:

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

Thanks in advance.

CodePudding user response：

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

CodePudding user response：

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.