Search code examples
sqlpostgresqlsumuniondistinct

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?

Solution

  • 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.