Question 114995 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – SQL Union and special sum

I’m new to SQL queries so hopes this question isn’t stupid.

I got two tables like this:

Table 1:

Name Value Count
global g 1
domain x 2
domain y 1
agg ba 1

Table 2:

Name Value Count
global g 1
domain z 1
agg bb 1

I need to get this kind of table – which is consist of all rows without duplications, and the global row should changed it’s count to the sum of the ‘domain’ rows from the first table only:

Table 3:

Name Value Count
global g 3
domain x 2
domain y 1
domain z 1
agg ba 1
agg bb 1

is this kind of operation is possible?

Tags:

2

Answers


  1. 0

    demo:db<>fiddle

    SELECT * FROM table1
WHERE "Name" <> 'global'    -- 1

UNION

SELECT                      -- 2
    'global', 
    'g',
    SUM("Count")
FROM table1
WHERE "Name" = 'domain'
 
UNION

SELECT * FROM table2
WHERE "Name" <> 'global'   -- 1
    1. Union both tables without the global row
    2. Create a new global row for the expected sum of the table1 domain records. Union it as well.
    Login or Signup to reply.
  2. 0

    Try this out

    SELECT x.name, x.total_val, sum(occurence)
FROM (SELECT name, total_val, occurence FROM test union all select name, total_val, occurence from test2) x
group by x.name, x.total_val

    You can check on this db fiddle as well test case

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search