Substring statement

Question

Post reply

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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Ken McKelvey SSCoach Points: 18856 More actions · Answer 1

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 · Answer 2

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

Tiffany

Jeff Moden SSC Guru Points: 1004407 More actions · Answer 3

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)