By ayrton


2018-11-08 23:02:02 8 Comments

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?

2 comments

@Gordon Linoff 2018-11-08 23:09:03

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;

@mypetlion 2018-11-08 23:10:50

How would this sum the value column?

@Gordon Linoff 2018-11-08 23:15:58

@mypetlion . . . It does now.

@ayrton 2018-11-08 23:19:46

Would that work if I were using two tables that depended on caixa instead of just one, each with its own type field?

@Gordon Linoff 2018-11-09 00:28:00

@ayrton . . . That depends. If you have another question, you should ask it as a new question, with appropriate sample data and desired results.

@mansio 2018-11-08 23:04:54

You can do multiple OR

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

Or use BETWEEN

SELECT SUM(value) FROM movimentacao where id_caixa = x.id_caixa and type BETWEEN 0 AND 3

@mypetlion 2018-11-08 23:08:32

type = 0 and type = 1 and type = 3 will always be false. Further, if OP is removing the subquery, where id_caixa = x.id_caixa will make the query invalid.

Related Questions

Sponsored Content

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

12 Answered Questions

[SOLVED] MySQL - UPDATE query based on SELECT Query

7 Answered Questions

19 Answered Questions

[SOLVED] SQL update from one Table to another based on a ID match

6 Answered Questions

[SOLVED] How do I add indices to MySQL tables?

44 Answered Questions

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

6 Answered Questions

[SOLVED] SQL query return data from multiple tables

  • 2012-09-18 11:11:39
  • Fluffeh
  • 708439 View
  • 395 Score
  • 6 Answer
  • Tags:   mysql sql select

8 Answered Questions

[SOLVED] SQL to find the number of distinct values in a column

  • 2008-09-26 19:52:48
  • Christian Oudard
  • 448138 View
  • 288 Score
  • 8 Answer
  • Tags:   sql distinct

1 Answered Questions

[SOLVED] Selecting multiple columns/fields in MySQL subquery

  • 2011-04-16 11:52:54
  • Martin
  • 118107 View
  • 45 Score
  • 1 Answer
  • Tags:   mysql

Sponsored Content