Complex Group By Statement

[Ann22]

Hello all -

I am using DB2 UDB 9
Few things on this one....

(1). I'd have to somehow merge the following 2 selects:

1. SELECT   usr_empl_name, period
, COUNT   (SSN) as MEMBERS
, case app when 'SFHF' THEN 'FAMILY HIGH' when 'SFHS' then 'SINGLE HIGH' else 'SINGLE HIGH' end AS COV_TYPE
FROM umass_contr , umass_empl
        WHERE
employer=empl and empl='9990002' and period = date('2005-07-01')   and  APP IN ('SFHS', 'SFHF' )  
GROUP BY usr_empl_name,  period,   app 


2.
select  usr_empl_name, period
, COUNT(SSN)
, marit
--, case marit when 'A' THEN 'FAMILY HIGH'  else 'SINGLE HIGH' end AS COV_TYPE
FROM umass_contr , umass_empl
        WHERE
employer=empl and empl='9990002' and period = date('2005-07-01')   and  APP IN ( 'FLAT' )  and period = date('2005-07-01') and employer='9990002'
GROUP BY  (usr_empl_name,period, marit)  
order by COV_TYPE

(2). The result of the 2nd select creates:

USR_EMPL_NAME PERIOD 3 MARIT
------------ ---------- ----------- -----
UMASS MEDICAL 07/01/2005 1
UMASS MEDICAL 07/01/2005 44M
UMASS MEDICAL 07/01/2005 13S
UMASS MEDICAL 07/01/2005 3 U


How do I create a result table with only 2 records ?

USR_EMPL_NAME PERIOD 3 MARIT

UMASS MEDICAL 07/01/2005 17 SINGLE
UMASS MEDICAL 07/01/2005 44 MARRIED


THANKS FOR YOUR HELP!

Ann.

[skhanal]

1. Can't you use UNION between two SELECTs
2. What's the criteria to get 2 records only?. Put that in WHERE clause.

[Ann22]

thanks, i used union