SQL Union and special sum-CodePudding

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?

CodePudding user response：

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

Union both tables without the global row
Create a new global row for the expected sum of the table1 domain records. Union it as well.

CodePudding user response：

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