Sqlite how Select sum() from two tables in one query

user_14101645 0
Viewed 45

I'm struggling with this query:

SELECT 
  case strftime('%m',Expenses.Date)  
    when '01' then 'January' 
    when '02' then 'Febuary' 
    when '03' then 'March' 
    when '04' then 'April' 
    when '05' then 'May' 
    when '06' then 'June' 
    when '07' then 'July' 
    when '08' then 'August' 
    when '09' then 'September' 
    when '10' then 'October' 
    when '11' then 'November' 
    when '12' then 'December' 
    else '' 
  end as monthName ,
  strftime('%m',Expenses.Date) as monthnum,
  SUM(Expenses.Amount),
  SUM(Incomes.Amount)
FROM Expenses JOIN Incomes 
ON Expenses.UserID = Incomes.UserID
GROUP BY strftime('%m',Expenses.Date), strftime('%m',Incomes.Date)

What I'm trying to do is to get sum() of the column Amount from the table Expenses and the sum() of the column Amount from the table Incomes as two different columns in one query but I'm running to an error. Please if someone can help me.

sqlite
1 Answers

Use a CROSS join of all the user ids and a query that returns all the month numbers and names and then LEFT joins to queries that aggregate in each of the tables separately:

WITH
  m(monthnum, monthname) AS (
    VALUES ('01', 'January'), ('02', 'Febuary'), ('03', 'March'), ('04', 'April'),
           ('05', 'May'), ('06', 'June'), ('07', 'July'), ('08', 'August'), 
           ('09', 'September'), ('10', 'October'), ('11', 'November'), ('12', 'December')
  ),
  u AS (
    SELECT UserID FROM Expenses
    UNION
    SELECT UserID FROM Incomes
  ),  
  e AS (
    SELECT UserID, strftime('%m', Date) AS monthnum, SUM(Amount) Amount
    FROM Expenses
    GROUP BY UserID, monthnum
  ),
  i AS (
    SELECT UserID, strftime('%m', Date) AS monthnum, SUM(Amount) Amount
    FROM Incomes
    GROUP BY UserID, monthnum
  )
  
SELECT u.UserID, m.monthnum, m.monthname,
       COALESCE(e.Amount, 0) expenses_amount,
       COALESCE(i.Amount, 0) income_amount
FROM m CROSS JOIN u
LEFT JOIN e ON e.monthnum = m.monthnum AND e.UserID = u.UserID
LEFT JOIN i ON i.monthnum = m.monthnum AND i.UserID = u.UserID
ORDER BY u.UserID, m.monthnum
user_104988280
Related