How can I get the difference of 2 variables in Microsoft SQL?

NateMacDonald1 0
Viewed 26

I am trying to find the difference between the revenue of customers who have not churned vs customers who have. This is what I have:

 Select sum ([monthly charge]) as Non_Churned_Revenue
from portfolioproject..telecom_churn
Where [Customer Status] = 'Joined' OR [Customer Status] = 'Stayed'

Select sum ([monthly charge]) as Churned_Revenue
from portfolioproject..telecom_churn
Where [Customer Status] = 'Churned'

How would I be able to subtract Churned_Revenue from Non_Churned_Revenue? I keep getting errors when I try.

Thanks!

sql-server sql
1 Answers

You are looking for conditional aggregation

SELECT
  SUM(CASE WHEN tc.[Customer Status] IN ('Joined', 'Stayed') THEN tc.[monthly charge] END) AS Non_Churned_Revenue
  SUM(CASE WHEN tc.[Customer Status] = 'Churned' THEN tc.[monthly charge] END) AS Churned_Revenue,
  SUM(CASE WHEN tc.[Customer Status] IN ('Joined', 'Stayed') THEN tc.[monthly charge] END)
    -
    SUM(CASE WHEN tc.[Customer Status] = 'Churned' THEN tc.[monthly charge] END) AS Difference
FROM telecom_churn tc;
Charlieface0
Related