How to make a query with multiple sums in a single row without subqueries?

user_6743831 0
Viewed 50

I need a query which returns four different sums based on a column, but don't know how to do it without subqueries.

What I need to do is basically this: I have two tables, "caixa" and "movimentacao". The table "movimentacao" has a field "type", which ranges from 0 to 3, and a field "value", and need a query which returns the sum of value from each type of "movimentacao" belonging to a "caixa", but in a single row, so grouping by the type won't work.

So far my solution is:

SELECT
    (SELECT SUM(value) FROM movimentacao where id_caixa = x.id_caixa and type = 0)
    (SELECT SUM(value) FROM movimentacao where id_caixa = x.id_caixa and type = 1)
    (SELECT SUM(value) FROM movimentacao where id_caixa = x.id_caixa and type = 2)
    (SELECT SUM(value) FROM movimentacao where id_caixa = x.id_caixa and type = 3)
FROM caixa x

Is there a way to do this without these four subqueries, using joins instead?

mysql sql
1 Answers

You can do this using aggregation:

SELECT SUM( CASE WHEN m.type = 0 THEN x.value END ) as type_0,
       SUM( CASE WHEN m.type = 1 THEN x.value  END ) as type_1,
       SUM( CASE WHEN m.type = 2 THEN x.value  END ) as type_2,
       SUM( CASE WHEN m.type = 3 THEN x.value  END ) as type_3
FROM caixa x JOIN
     movimentacao m
     ON x.id_caixa = m.id_caixa;
user_11440350
Related