CASE STATEMENT WITH GROUP BY 2

[cristi22]

Hello all -

Thanks for your help in advance!!!

Here is my code:

select    
employer,  period, COUNT( DISTINCT ssn ),
CASE marit when 'M' then 'MARIT' when 'S' then 'SINGLE' else 'SINGLE' END  as MARIT_ST from umass_contr 
where  APP IN ('FLAT' )  and period = date('2005-07-01') and employer='9990002'
 GROUP BY employer,period, marit

.... & results:

EMPLOYER  PERIOD     3           MARIT_ST
--------- ---------- ----------- --------
9990002   07/01/2005          44 MARIT   
9990002   07/01/2005           1 SINGLE  
9990002   07/01/2005          13 SINGLE  
9990002   07/01/2005           3 SINGLE

How do I change my statement to produce the following:

9990002 07/01/2005 44 MARIT
9990002 07/01/2005 17 SINGLE

THANKS A LOT!

 

[MarcLodge]

Hi Cristi,
It looks to me as if it's the value of ssn that is causing you the problems. I would guess that all the rows where Marit is anything other than 'M', have 3 different values of Marit, giving you your 3 rows. You could try replacing COUNT(DISTINCT SSN) with COUNT(*), but I suspect that will count all the rows and give you a much higher number than you require. What I would suggest you do is to embed to query you have already written into a further outer query, where you sum the count. I'm not near a DB2 system at the moment so can't validate the following, but if not 100% syntactically correct, it should give you and idea of what I getting at!!

select employer, period, SUM(noofssns) from

  (select employer,  period, COUNT(DISTINCT ssn) noofssns,
   CASE marit when 'M' then 'MARIT'
   when 'S' then 'SINGLE'
   else 'SINGLE' END  as MARIT_ST from umass_contr 
   where  APP IN ('FLAT' )
   and period = date('2005-07-01')
   and employer='9990002'
   GROUP BY employer,period, marit )
   as temp_tab

group by employer, period, marit_st

 

[cristi22]

Marc - Thank you very much for you help!

Here is an error msg:

SQL0119N  An expression starting with "PERIOD                     
            " specified in a 
SELECT clause, HAVING clause, or ORDER BY clause is not 
specified in the GROUP BY clause or it is in a SELECT clause, 
HAVING clause, or ORDER BY clause with a column function and no 
GROUP BY clause is specified.

 

[cristi22]

Actually, I have to combine the two select statements

APP = SFHS then SINGLE HIGH
APP = SFHF then FAMILY HIGH

APP = FLAT IF MARIT = ‘A’ then FAMILY STAN
APP = FLAT IF MARIT = ‘C’ then SINGLE STAN

select  employer,  period
,COUNT  ( case when  marit = 'M' then  ssn   when marit= 'S' then ssn  else ssn end) , app
 from umass_contr
where  APP IN ( 'FLAT' )  and period = date('2005-07-01') and employer='9990002'
GROUP BY employer,period, app, MARIT

SELECT   usr_empl_name, period, app
, COUNT   ( case app when 'SFHS' then   ssn   end )
, COUNT  ( case app  when 'SFHF' then ssn  end)
-- , COUNT  (case app when 'FLAT' then  ssn end)
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

for the result to be:

USR_EMPL_NAME  FAMILIY_HIGH SINGLE_HIGH FAMILY_STD SINGLE_STD
-----------------------------------------------------------
UMASS MEDICAL      11           18           0          5

PLEASE HELP!!!!

thanks, Cristi

 

[BrianTyler]

Case statements can be nested, try something like:


SELECT usr_empl_name, period, app
, COUNT (case app when 'SFHS' then ssn end )
, COUNT (case app when 'SFHF' then ssn end)
, COUNT (case app when 'FLAT' then
case MARIT when 'A' then ssn end
end)
, COUNT (case app when 'FLAT' then
case MARIT when 'C' then ssn end
end)
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

 

[MarcLodge]

Hi Cristi,
I'll try to get rid of the error message first. How about:

select employer_temp, period_temp, SUM(noofssns) from

  (select employer as employer_temp,  period as period_temp, 
   COUNT(DISTINCT ssn) as noofssns,
   CASE marit when 'M' then 'MARIT'
   when 'S' then 'SINGLE'
   else 'SINGLE' END  as MARIT_ST from umass_contr 
   where  APP IN ('FLAT' )
   and period = date('2005-07-01')
   and employer='9990002'
   GROUP BY employer,period, marit )
   as temp_tab

group by employer_temp, period_temp, marit_st

Let me know if that works, and I'll then look at the next bit, although Brian's idea looks good.
Marc

 

[cristi22]

THANKS YOU VERY MUCH FOR YOUR HELP!

Brian's solution with minor changes produced exatly what I needed!!!!

SELECT   usr_empl_name, period, app
, COUNT  (case app when 'SFHS' then  ssn   end )
, COUNT  (case app  when 'SFHF' then ssn  end)
, sum  (case app when 'FLAT' then 
            case MARIT when 'M'  then 1 else 0 end
          end)
, sum  (case app when 'FLAT' then 
            case MARIT when 'M' then 0 else 1 end
          end)
FROM umass_contr , umass_empl
        WHERE
employer=empl and empl='9990002'
and period = date('2005-07-01')  
and  APP IN ('SFHS', 'SFHF' , 'FLAT' )  GROUP BY
usr_empl_name,  period,   app

...few more things

Result:

USR_EMPL_NAME PERIOD  APP  4   5  6 7          
 ----------- ----------- -----------
UMASS MEDICAL 07/01/2005 FLAT 0  0 44 17
UMASS MEDICAL 07/01/2005 SFHF 0 18 -  -
UMASS MEDICAL 07/01/2005 SFHS 11 0 - -

1. How to add column names instead of 4 5 6 7
2. I need incorporate the following

records with 'NK' status supposed to added to the report

???? union ???? or to add to the first st?????

select usr_empl_name, period,count (distinct  ssn)
FROM umass_contr , umass_empl
       WHERE
employer=empl and empl='9990002'  and nk_type='NK'  and period = date('2005-07-01')  and  APP IN ( 'FLAT' )  
GROUP BY usr_empl_name,  period

thanks a lot!
Cristi

 

[cristi22]

HERE IS MY SOLUTION:

SELECT   usr_empl_name, period, 
(case app when 'SFHS' then 'High_Single'
when 'SFHF' then 'High_Family'
when  'FLAT' then (case MARIT when 'M'  then 'Stan_Family' else 'Stan_Single' end)
else ''
end) plandesc,
Count(1) tot_cnt
FROM umass_contr , umass_empl
        WHERE
employer=empl and empl='9990002'
and period = date('2005-07-01')  
and  APP IN ('SFHS', 'SFHF' , 'FLAT' )  
GROUP BY
usr_empl_name,  period,   
(case app when 'SFHS' then 'High_Single'
when 'SFHF' then 'High_Family'
when  'FLAT' then (case MARIT when 'M'  then 'Stan_Family' else 'Stan_Single' end)
else ''
end)
union
select usr_empl_name, period,'NK' as plandesc, count (distinct ssn) as tot_cnt
FROM umass_contr , umass_empl
        WHERE
employer=empl and empl='9990002'
and period = date('2005-07-01')  
and  APP IN ( 'FLAT' )  
GROUP BY
usr_empl_name,  period