Combined Queries

Question

Very new to MYSQL, but how can I combine these two queries to show the SUM(total) of the type = 'i' minus SUM(total) of the type = 'p'.

SELECT userid, SUM(total)
FROM invoices
WHERE type = 'i'
GROUP BY 1

SELECT userid, SUM(total)
FROM invoices
WHERE type = 'p'
GROUP BY 1

Answer 1

You could use case expression

SELECT userid, 
    SUM(case when type = 'i' then total else 0 end) typei,
    SUM(case when type = 'p' then total else 0 end) typep,
    SUM(case when type = 'i' then total else 0 end) - SUM(case when type = 'p' then total else 0 end) as diff
FROM invoices
GROUP BY userid

Or you could write it as

SUM(case when type = 'i' then total when type = 'p' then -1 * total else 0 end) as diff

Edit: To filter the results produced by aggregation you could use having clause

SELECT userid, 
    SUM(case when type = 'i' then total else 0 end) typei,
    SUM(case when type = 'p' then total else 0 end) typep,
    SUM(case when type = 'i' then total else 0 end) - SUM(case when type = 'p' then total else 0 end) as diff
FROM invoices
GROUP BY userid
HAVING diff > 0