How can I perform subtraction across multiple grouped columns in SQL?

Data and Databases
1

I’m trying to calculate values in the following manner for each date: result = X - (Y + Z). Here’s the SQL code I’m using:

WITH revenue_calculation AS (
  SELECT
    DATE_FORMAT(created_at, '%Y-%m-%d') AS date,
    (SELECT SUM(IF(category = 'revenue', amount, 0)) FROM transactions WHERE category = 'revenue')
    - (SELECT SUM(IF(category = 'costs', amount, 0)) FROM transactions WHERE category = 'costs')
    - (SELECT SUM(IF(category = 'charges', amount, 0)) FROM transactions WHERE category = 'charges') AS result,
  GROUP BY date
  ORDER BY date DESC
)
SELECT * FROM revenue_calculation;

However, this code isn’t working; I’m getting an error that says: mismatched input 'AS'. Expecting: <expression>. What steps can I take to resolve this issue?

2

you might wanna check your subqueries, they should be in FROM or JOIN clauses. Move these to the main query with respective joins, it should fix the ‘AS’ error. also, don’t forget to group by the same columns used in the SELECT clause and check your date formatting!

3

The issue seems to stem from how subqueries are being utilized in your SQL code. Instead of having individual subqueries for each category within the same SELECT statement, consider using a case statement within a SUM function in a single query. This approach promotes clarity and reduces the error potential. You could structure your query by incorporating a GROUP BY clause focusing on the date and summing based on conditional expressions that handle each category separately. Also, ensure that there’s no mismatch in your column aliases.

4

Hmm interesting situation you’ve got there! Where you place the subquery might just be key here. Have you tried restructuring it by using common table expressions (CTEs) for each category then performing the subtraction in the main SELECT? Could open up other perspectives!