October 6, 1999 10:43 AM
(in response to Shabbir (S.) Mastan)
Probably the best way to do this is to use a program, but if
isn't feasible, then another option may be:
,COALESCE(NST1.CNT,0) + COALESCE(NST2.CNT,0)
FROM (SELECT FLD1, FLD2, COUNT(*) AS CNT
GROUP BY FLD1, FLD2) AS NST1
FULL OUTER JOIN
(SELECT FLD1, FLD2, COUNT(*) AS CNT
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.
All opinions are my own, not those of my employer.
"Mastan, Shabbir (S.)" <[login to unmask email]> on 10/06/99
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
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
is this possible in conjunction with unioning 2 tables.
Any help/suggestions will be welcome.
[login to unmask email]