Substring statement

• tiffany.franks

  Newbie

  Points: 7

  More actions

  August 6, 2009 at 9:09 am

  #209057

  Here is the query I'm using in order to get headcounts of students by groups (first time freshmen, new transfers, new others, and new graduate students). However, I want the counts for each group by term and the query is summing the counts for all 3 terms together, so the counts are the same for every semester. Any suggestions on how to get counts by term rather than all 3 terms together?

  select STVTERM_DESC,(select count (SPBPERS_PIDM)

  from WSUSTU.MWT_STU20

  where FIRST_TIME_FRESH_IND = 'Y') as New_First_Time_Fresh,

  (select count (SPBPERS_PIDM)

  from WSUSTU.MWT_STU20

  wheresubstring (STVTERM_CODE, 5,2)= '10'

  and NEW_TRANSFER_IND = 'Y') as New_Transfer,

  (select count (SPBPERS_PIDM)

  from WSUSTU.MWT_STU20

  wheresubstring (STVTERM_CODE, 5,2)= '10'

  and NEW_OTHER_IND = 'Y') as New_Other,

  (select count (SPBPERS_PIDM)

  from WSUSTU.MWT_STU20

  wheresubstring (STVTERM_CODE, 5,2)= '10'

  and NEW_GRAD_IND = 'Y') as New_Grad

  from WSUSTU.MWT_STU20

  wheresubstring (STVTERM_CODE, 5,2)= '10'

  group by STVTERM_DESC

  Here is the result set from the query above:

  Fall 20064882399512682063

  Fall 20074882399512682063

  Fall 20084882399512682063

• Ken McKelvey

  SSCoach

  Points: 18896

  More actions

  August 6, 2009 at 9:49 am

  #1035486

  Difficult to tell without sample data and expected results, but you seem not to be restricting the subqueries

  by the STVTERM_DESC. In this case I would forget about subqueries and just use CASE.

  Something along the lines of:

  SELECT STVTERM_DESC

  ,COUNT(CASE WHEN FIRST_TIME_FRESH_IND = 'Y' THEN 1 END) AS New_First_Time_Fresh

  ,COUNT(CASE WHEN NEW_TRANSFER_IND = 'Y' THEN 1 END) AS New_Transfer

  ,COUNT(CASE WHEN NEW_OTHER_IND = 'Y' THEN 1 END) AS New_Other

  ,COUNT(CASE WHEN NEW_GRAD_IND = 'Y' THEN 1 END) AS New_Grad

  FROM WSUSTU.MWT_STU20

  WHERE SUBSTRING(STVTERM_CODE, 5,2)= '10'

  GROUP BY STVTERM_DESC

• tiffany.franks

  Newbie

  Points: 7

  More actions

  August 6, 2009 at 10:02 am

  #1035500

  Thank you Ken, you are a genius! This works great, I'm still learning, I appreciate the help!

  Tiffany

• Jeff Moden

  SSC Guru

  Points: 1004591

  More actions

  August 6, 2009 at 12:57 pm

  #1035616

  tiffany.franks (8/6/2009)

  ---

  Thank you Ken, you are a genius! This works great, I'm still learning, I appreciate the help!

  Tiffany

  Tiffany,

  Just for further reference, the method Ken used is known as a "Cross Tab".

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)