Merging two unrelated query results into single table - how?

Question

I am trying to merge the results of two separate queries that are run on two different tables of the same DB.

Table 1 is the result of the following query:

SELECT db1.table1.AAA AS Col1, SUM(db1.table1.BBB) AS Col2
    FROM db1.table1
    WHERE cond1 AND cond2
    GROUP BY db1.table1.AAA
    ORDER BY db1.table1.AAA

Result:

Col 1    Col2
ABC      1200
BFK      5405
KVR      1254
LRE      9851
(The list goes on)

Table 2 is the result of another query on another table:

SELECT SUM(db1.table2.CCC), SUM(db1.table2.DDD)
    FROM db1.table2
    WHERE cond3

Result:

CCC      DDD
1325     5428

What I am trying to do is getting a table where this second result is added to the other table, transposed, like so:

Col 1    Col2
ABC      1200
BFK      5405
KVR      1254
LRE      9851
CCC      1325
DDD      5428

I have been experimenting with UNION, PIVOT, INSERT, etc. to no avail, and I am absolutely sure that i don't have a clear view on how this could/should work.

Currently i run both queries one after the other and then manually insert those 4 pieces of data from query2 into the result of query1.

Is there a more "elegant" way, where one SQL query would return the same result?

I am working on this in MS Server Management Studio - and also testing in a small tool written by a colleague (the tool handles xml files with the SQL codes in them(.

Could you please add what database system are you using (Postgres, MySQL, Oracle...)? — TheWildHealer, Apr 08 '19 at 10:01
And the CCC and DDD rows should really be at the end, i.e. not included in the ORDER BY? — jarlh, Apr 08 '19 at 10:04
TheWildHealer: Thanks, added that info to my OP jarlh: yes, that just has to be added as two new rows, regardless of everything — szhep, Apr 08 '19 at 10:05

score 2 · Accepted Answer · answered Apr 08 '19 at 10:05

2

One method uses UNION ALL:

SELECT t1.AAA AS Col1, SUM(t1.BBB) AS Col2
FROM db1.table1 t1
WHERE cond1 AND cond2
GROUP BY db1.table1.AAA
UNION ALL
SELECT 'CCC', SUM(t2.CCC)
FROM db1.table2 t2
WHERE cond3
UNION ALL
SELECT 'DDD', SUM(t2.DDD)
FROM db1.table2 t2
WHERE cond3;

In SQL Server, you can also express this as:

SELECT t1.AAA AS Col1, SUM(t1.BBB) AS Col2
FROM db1.table1 t1
WHERE cond1 AND cond2
GROUP BY db1.table1.AAA
UNION ALL
SELECT v.col1, v.sumcol
FROM (SELECT SUM(t2.CCC) as ccc, SUM(t2.DDD) as ddd
      FROM db1.table2 t2
      WHERE cond3
     ) t2 CROSS APPLY
     (VALUES ('CCC', t2.ccc), ('DDD', t2.ddd)) v(col1, sumcol)

answered Apr 08 '19 at 10:05

Gordon Linoff

1,242,037
58
646
786

I didn't know about `UNION ALL` but it seems to be better on the performance POV: https://stackoverflow.com/a/49928/1441729 – kant312 Apr 08 '19 at 10:09
Thank you, Gordon, works like a charm. I see I overcomplicated the whole thing... – szhep Apr 08 '19 at 12:18

score 0 · Answer 2 · answered Apr 08 '19 at 10:05

0

You could use two unions and insert the name of the columns in the select:

SELECT 
    db1.table1.AAA AS Col1, 
    SUM(db1.table1.BBB) AS Col2
FROM 
    db1.table1
WHERE 
    cond1 AND cond2
GROUP BY 
    db1.table1.AAA
ORDER BY 
    db1.table1.AAA

UNION

SELECT 
    'CCC',
    SUM(db1.table2.CCC)
FROM 
    db1.table2
WHERE 
    cond3

UNION

SELECT 
    'DDD',
    SUM(db1.table2.DDD)
FROM 
    db1.table2
WHERE 
    cond3

answered Apr 08 '19 at 10:05

kant312

1,114
1
15
28

1

Thank you, kant. Your solution works as well too. As i said in my comment on the other solution, i seem to have overthought it. – szhep Apr 08 '19 at 12:19

Merging two unrelated query results into single table - how?

2 Answers2