How to combine 3 different tables into one table to create aggregated data?
P粉891237912
P粉891237912 2024-03-31 12:45:39
0
1
355

Hope you all are doing well! :D

I need your help to complete the following tasks:

I need to create the following table:

date Revenue from new transactions Lost revenue due to churn Revenue from upsells
January 1, 2022 1,000 -$500 1,000
January 2, 2022 $2000 -$200 $2000

What happens here is that in order to collect and aggregate this data, I need to get 3 different tables:

Transactions, Churn and Upsells

TransactionTable:

trade expiration date Earnings won
Transaction#1 January 1, 2022 $500
Transaction#2 January 1, 2022 $500
Transaction #3 January 2, 2022 $1500
Transaction#4 January 2, 2022 $500

LossTable:

churn expiration date Loss of income
churn#1 January 1, 2022 -$500
churn#2 January 2, 2022 -100 dollars
churn#3 January 2, 2022 -100 dollars

Upsell list:

Upsell expiration date Earnings won
Upsell#1 January 1, 2022 $2000
Upsell #2 January 1, 2022 -1,000
Upsell#3 January 2, 2022 $2000

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

Thanks in advance.

P粉891237912
P粉891237912

reply all(1)
P粉674999420

You can use subqueries to aggregate the churn table and the upsell table.

As follows:

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 dates as text, you will face a lot of difficulties. If you want to format the date you can use DATE_FORMAT

Notice. If another date exists in the transaction table but not in the other two tables, that date will be filtered out from the results. If you want it, use LEFT JOIN instead of 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

Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!