How to subtract multiple grouped columns in SQL-CodePudding

I want to perform aggregations in the following format for each date: result = A - (B C)

I have the following query for that:

WITH
  net_revenue_calculator AS (
    SELECT(
     DATE_FORMAT(created, '%Y-%m-%d') AS "date",
    (SELECT 
      SUM(IF(reporting_category = 'platform_earning', net, 0)) AS "gross_revenue"
    FROM balance_transactions
    WHERE
      reporting_category = 'platform_earning'
    )
    -
    (SELECT 
    SUM(IF(reporting_category = 'network_cost', net, 0)) AS "network_fees"
    FROM balance_transactions
    WHERE
      reporting_category = 'network_cost'
    )
    -
     (SELECT 
        SUM(IF(reporting_category = 'fee', net, 0)) AS "stripe_fees"
    FROM balance_transactions
    WHERE
            reporting_category = 'fee'
   )
   AS DIFFERENCE ,
   GROUP BY 1,
    ORDER BY 1 DESC   
    )
 )
 
select * from net_revenue_calculator

This is failing and says: mismatched input 'AS'. Expecting: <expression>

How can I fix this?

CodePudding user response：

I think you want conditional aggregation here:

SELECT
    created,
    SUM(IF(reporting_category = 'platform_earning', net, 0)) AS gross_revenue,
    SUM(IF(reporting_category = 'network_cost', net, 0)) AS network_fees,
    SUM(IF(reporting_category = 'fee', net, 0)) AS stripe_fees
FROM balance_transactions
WHERE
    reporting_category IN ('platform_earning', 'network_cost', 'fee')
GROUP BY
    created;

If you want the A - (B C) version, then use:

SELECT
    created,
    SUM(IF(reporting_category = 'platform_earning', net, 0)) -
    SUM(IF(reporting_category = 'network_cost', net, 0)) -
    SUM(IF(reporting_category = 'fee', net, 0)) AS net_earnings
FROM balance_transactions
WHERE
    reporting_category IN ('platform_earning', 'network_cost', 'fee')
GROUP BY
    created;