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

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!

CodePudding user response：

You could use a CTE... https://learnsql.com/blog/what-is-common-table-expression/ with a union as long as your columns line up.

with cte as (

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

union all

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

)
select sum(revenue) from cte

CodePudding user response：

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;