CODEWITHSUNDEEP
sqlpostgresql
Luigi Falco
Luigi Falco

Reputation: 65

union all and sum

I have 2 tables:

table 1

mmsi   type   l   w   flag   value
123    456    A   B   C      37
236    567    X   Y   Z      28

table 2

mmsi   type   l   w   flag   value
123    456    A   B   C      30
234    567    X   Y   Z      20

and I want to run a query to get unique values and for each row the sum of values stored in the last field:

table final

mmsi   type   l   w   flag   value
123    456    A   B   C      67
234    567    X   Y   Z      20
236    567    X   Y   Z      28

How can I do it? I am trying with UNION ALL and SUM

SELECT "mmsi", "type", "l", "w", "flag", "value", SUM ("value")
FROM (SELECT "mmsi", "type", "l", "w", "flag", "value" FROM "table 1" UNION ALL
SELECT "mmsi", "type", "l", "w", "flag", "value" FROM "table 2") as def
GROUP BY "mmsi", "type", "l", "w", "flag", "value"

but this give me the following wrong result:

mmsi   type   l   w   flag   value  sum
123    456    A   B   C      37     37
123    456    A   B   C      30     30
234    567    X   Y   Z      20     20
236    567    X   Y   Z      28     28

Thanks!

Upvotes: 0

Views: 84

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You need to remove value from the select and group by:

SELECT "mmsi", "type", "l", "w", "flag", SUM("value")
FROM (SELECT "mmsi", "type", "l", "w", "flag", "value" FROM "table 1" 
      UNION ALL
      SELECT "mmsi", "type", "l", "w", "flag", "value" FROM "table 2"
     ) as def
GROUP BY "mmsi", "type", "l", "w", "flag";

Upvotes: 3

Related Questions