Highlighted

SUM DISTINCT CASE

Explorer ,
Sep 09, 2016

Copy link to clipboard

Copied

I need to report counts by program, level and if they have enrolled in a course for the selected year. With the below query I receive counts for both the enrolled and offered columns when a student is enrolled in the selected year and also previous years. It does not seem to select only the MAX(nmcrs.sch_yr). If this is not the proper way to achieve my results I am willing to change!

SELECT distinct award.awd_cod, name.soc_sec, award.awd_txt, MAX(nmcrs.sch_yr),

/* Registered Returning Students Count */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_FRSO_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_JR_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_SR_Cnt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR'  AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Enrl_TR_Cnt,

/* Registered Returning Students Amount */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_FRSO_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_JR_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_SR_Amt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_TR_Amt,

/* Not Registered Returning Students Count */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_FRSO_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_JR_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_SR_Cnt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR'  AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_TR_Cnt,

/* Not Registered Returning Students Amount */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_FRSO_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_JR_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <>transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_SR_Amt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_TR_Amt

FROM award INNER JOIN transact ON award.awd_cod = transact.awd_id

     INNER JOIN rpt_schy ON transact.sch_yr = rpt_schy.sch_yr

     INNER JOIN name ON transact.soc_sec = name.soc_sec

     INNER JOIN nmcrs ON name.soc_sec = nmcrs.soc_sec

     INNER JOIN nmprg ON name.soc_sec = nmprg.soc_sec

WHERE nmprg.active = '1'

   AND nmcrs.transfer = '0'

   AND transact.accept <> 'v'

   AND transact.tcodes = 'AW'

   AND (award.award_type_type = 3 OR award.award_type_type = 6)

   AND (name.camp_cod='Eureka' OR name.camp_cod='TBD')

   AND name.last_name not like '%Teste%'

   AND rpt_schy.token = '#session.token#'

GROUP BY name.soc_sec, award.awd_cod, award.awd_txt

ORDER BY award.awd_txt, name.soc_sec

I sum the counts and amounts in my report.

TOPICS
Reporting

Views

848

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

SUM DISTINCT CASE

Explorer ,
Sep 09, 2016

Copy link to clipboard

Copied

I need to report counts by program, level and if they have enrolled in a course for the selected year. With the below query I receive counts for both the enrolled and offered columns when a student is enrolled in the selected year and also previous years. It does not seem to select only the MAX(nmcrs.sch_yr). If this is not the proper way to achieve my results I am willing to change!

SELECT distinct award.awd_cod, name.soc_sec, award.awd_txt, MAX(nmcrs.sch_yr),

/* Registered Returning Students Count */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_FRSO_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_JR_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then 1 ELSE 0 END)) AS Enrl_SR_Cnt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR'  AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Enrl_TR_Cnt,

/* Registered Returning Students Amount */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_FRSO_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_JR_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_SR_Amt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr = transact.sch_yr then transact.offered ELSE 0 END)) AS Enrl_TR_Amt,

/* Not Registered Returning Students Count */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_FRSO_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_JR_Cnt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_SR_Cnt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR'  AND nmcrs.sch_yr <> transact.sch_yr then 1 ELSE 0 END)) AS Ofr_TR_Cnt,

/* Not Registered Returning Students Amount */

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ < '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_FRSO_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ = '3' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_JR_Amt,

SUM(distinct (case when nmprg.prg_cod <> 'UNGRDTR' AND name.level_ > '3' AND nmcrs.sch_yr <>transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_SR_Amt,

SUM(distinct (case when nmprg.prg_cod = 'UNGRDTR' AND nmcrs.sch_yr <> transact.sch_yr then transact.offered ELSE 0 END)) AS Ofr_TR_Amt

FROM award INNER JOIN transact ON award.awd_cod = transact.awd_id

     INNER JOIN rpt_schy ON transact.sch_yr = rpt_schy.sch_yr

     INNER JOIN name ON transact.soc_sec = name.soc_sec

     INNER JOIN nmcrs ON name.soc_sec = nmcrs.soc_sec

     INNER JOIN nmprg ON name.soc_sec = nmprg.soc_sec

WHERE nmprg.active = '1'

   AND nmcrs.transfer = '0'

   AND transact.accept <> 'v'

   AND transact.tcodes = 'AW'

   AND (award.award_type_type = 3 OR award.award_type_type = 6)

   AND (name.camp_cod='Eureka' OR name.camp_cod='TBD')

   AND name.last_name not like '%Teste%'

   AND rpt_schy.token = '#session.token#'

GROUP BY name.soc_sec, award.awd_cod, award.awd_txt

ORDER BY award.awd_txt, name.soc_sec

I sum the counts and amounts in my report.

TOPICS
Reporting

Views

849

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Sep 09, 2016 0

Have something to add?

Join the conversation