How to subtract multiple grouped columns in SQL

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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0