SELECT WITH GROUP BY 1

[cristi22]

Hello all -

I need to UNION the following SELECT into the rest of my procedure

The output should look like:

employer   period    FAMILY SINGLE
9990001	   1-Jul-05  267     176

MY SELECT:

  SELECT
                e.employer ,
                e.period,
                count( distinct e.ssn) tot_cnt,
                (case e.app when 'SFHS' then 'HIGH_SINGLE' when 'SFHF' then 'HIGH_FAMILY'  else '' end) plandesc
        FROM umass_contr l inner join   umass_contr r on l.period = (date('2005-07-01') - 6 month)
                and r.period =(date('2005-07-01') - 1 month) and l.ssn = r.ssn
                and l.employer=r.employer
                inner join umass_contr e on e.ssn=l.ssn and e.ssn=r.ssn and l.employer=r.employer
                and e.employer=r.employer
        WHERE
                e.period = date('2005-07-01')
                and  e.APP IN ('SFHS', 'SFHF'  )

        GROUP BY
                e.employer,  e.period,
                (case e.app when 'SFHS' then 'HIGH_SINGLE'
                when 'SFHF' then 'HIGH_FAMILY' else '' end)

Here is the results from my select statment:

EMPLOYE  PERIOD	     TOT_CNT	PLANDESC
9990001	1-Jul-05	267	FAMILY
9990001	1-Jul-05	176	SINGLE

THANKS A LOT,
Cristi

 

[ddiamond]

Looks like you are trying to create a pivot table. Are FAMILY and SINGLE the only possible plan descriptions? Do you need to do this with SQL? It will be much easier to create the pivot table using either MS Excel or MS Access.

 

[BrianTyler]

The simplest approach to do this with SQL is to use a CASE statement.

Replace the existing CASE in the SELECT phrase by:

sum(case e.app when 'SFHS' then 1 else 0 end) as "SINGLE"
sum(case e.app when 'SFHS' then 0 else 1 end) as "Family"

Remove the existing CASE from the Group By