Grouping results of Union

Shabbir (S.) Mastan

Grouping results of Union
Below is an example of what I would get from 'unioning' 2 tables.

Fld1 Fld2 Count
------ ------ ---------
KEYA KEYB 12
KEYA KEYB 03
KEYC KEYD 22

what I want to end up with is:

KEYA KEYB 15
KEYC KEYD 22

Basically I want to group by the first two fields and sum the count field,
is this possible in conjunction with unioning 2 tables.

Any help/suggestions will be welcome.

Shabbir
[login to unmask email]

Joseph Burns

Re: Grouping results of Union
(in response to Shabbir (S.) Mastan)
Probably the best way to do this is to use a program, but if that
isn't feasible, then another option may be:

SELECT COALESCE(NST1.FLD1,NST2.FLD1)
,COALESCE(NST1.FLD2,NST2.FLD2)
,COALESCE(NST1.CNT,0) + COALESCE(NST2.CNT,0)
FROM (SELECT FLD1, FLD2, COUNT(*) AS CNT
FROM TAB1
WHERE ....
GROUP BY FLD1, FLD2) AS NST1
FULL OUTER JOIN
(SELECT FLD1, FLD2, COUNT(*) AS CNT
FROM TAB2
WHERE ...
GROUP BY FLD1, FLD2) AS NST2
ON NST1.FLD1 = NST2.FLD1
AND NST1.FLD2 = NST2.FLD2

I made some assumptions about the results that you got from
the union of the two tables...namely that they were counts.

Also, I would add a warning, that the OUTER JOIN is between two
nested tables, so there will be no index access for the outer
join. This could be a problem if either NST1 or NST2 retrieve
a large number of rows.

Good luck,
Joe Burns
Highmark BC/BS

All opinions are my own, not those of my employer.









"Mastan, Shabbir (S.)" <[login to unmask email]> on 10/06/99 08:37:36 AM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Joseph Burns/ISG/CORP/Highmark)
Subject: Grouping results of Union




Below is an example of what I would get from 'unioning' 2 tables.

Fld1 Fld2 Count
------ ------ ---------
KEYA KEYB 12
KEYA KEYB 03
KEYC KEYD 22

what I want to end up with is:

KEYA KEYB 15
KEYC KEYD 22

Basically I want to group by the first two fields and sum the count field,
is this possible in conjunction with unioning 2 tables.

Any help/suggestions will be welcome.

Shabbir
[login to unmask email]