Hi
I had posted this question earlier but could not get the solution, may be i was not clear with the doubts i had
I need to count the no of students for the different Intervention field which are like 14 different types
against the field gender (male and Female )and field Ethnicity (horizontal field headers) 5 different types below
A student can be Male and Hispanic Type 1
also
Male Hispanic Type 2
Male Hispanic Type 3
So his count is made in three places
I need to do this
for the whole District level
then for each Center under District level
then for each school under Center level
| |
| |
| DISTRICT SUMMARY | | | |
| Intervention | Total | Male | Female | Asian | Black | Hispanic | Indian | Multiracial | White |
| |
| | | | | | | | | |
| TYPE : 1 | 7 | 3 | 4 | 4 | 2 | 0 | 0 | 0 | 1 |
| TYPE :2 | 6 | 3 | 3 | 1 | 0 | 4 | 0 | 0 | 1 |
| TYPE : 3 | 3 | 1 | 2 | 1 | 2 | 0 | 0 | 0 | 0 |
| TYPE : 4 | 5 | 2 | 3 | 2 | 3 | 0 | 0 | 0 | 0 |
| TYPE : 5 | 1 | 0 | 1 | 1 | 0 | 0 | 0 | 0 | 0 |
| TYPE : 7 | 2 | 1 | 1 | 0 | 2 | 0 | 0 | 0 | 0 |
| TYPE : 8 | 1 | 1 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
| TYPE : 9 | 2 | 1 | 1 | 0 | 1 | 0 | 0 | 0 | 1 |
| TYPE : 14 | 1 | 1 | 0 | 0 | 1 | 0 | 0 | 0 | 0 |
Please help
Thanks
| What does your table(s) structure look like?
|||
Hi
I am joining these TWO tables based on student ID
The fields Ethnicity and field Gender both come from one table Table A
The field Intervention Types comes from Table B
Table B is
a group of three tables because the table that has intervention types does not have student_id
so I need to link it via a common field with another table
This below is how i am working on the code
select
SUBQUERY.student_id,
SUBQUERY.Intervention_ID,
STDM.student_ethnicity,
STDM.student_gender,
count( case when STDM.STUDENT_ETHNICITY in('Asian') then STDM.student_id else NULL end) as [Asian],
count( case when STDM.STUDENT_ETHNICITY in ('Black') then STDM.student_id else NULL end) as [Black],
count( case when STDM.STUDENT_ETHNICITY in('Hispanic') then STDM.student_id else NULL end) as [Hispanic],
count( case when STDM.STUDENT_ETHNICITY in('American Indian') then STDM.student_id else NULL end) as [American Indian],
count( case when STDM.STUDENT_ETHNICITY in('Multiracial') then STDM.student_id else NULL end) as [Multiracial],
count( case when STDM.STUDENT_ETHNICITY in('White') then STDM.student_id else NULL end) as [White],
count( case when STDM.STUDENT_GENDER in('M') then STDM.student_id else NULL end) as [Male],
count( case when STDM.STUDENT_GENDER in('F') then STDM.student_id else NULL end) as [Female]
from DW_student.dbo.Student STDM
right join
(select distinct
student_id,INTV.intervention_id from
WAIN.DBO.Meeting MTNG
inner join WAIN.DBO.Meeting_Intervention MTGI on MTNG.Meeting_ID = MTGI.Meeting_ID
inner join WAIN.DBO.Intervention INTV on MTGI.Intervention_ID = INTV.Intervention_ID
) SUBQUERY
on SUBQUERY.student_id = STDM.student_id
group by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
SUBQUERY.Intervention_ID,
STDM.STUDENT_ETHNICITY,
SUBQUERY.student_id,
STDM.STUDENT_GENDER
order by
STDM.SCHOOL_REGION,
STDM.SCHOOL_NUMBER,
STDM.SCHOOL_NAME,
STDM.STUDENT_ETHNICITY,
SUBQUERY.Intervention_ID asc,
SUBQUERY.student_id,
STDM.STUDENT_GENDER
|||This looks like it would be a lot easier to do in a cube or pivot table. Where type is your dimension on rows and ethnicity is your dimension on columns, and count would be the measure.|||
Can you please explain it relating it to the present example
Thanks
Sowmya
|||
Your logic is motly used when PIVOTing the Columns. I didn't find any table design changes here. Your data is perfect.
You are almost on correct track – Your logic & intention of the expression almost perfect. But you need to change the expression slightly to achive your result,
- Use 0 as ELSE value on the Case When
- Use Sum instead of Count
Here the updated query,
select
subquery.student_id,
subquery.intervention_id,
stdm.student_ethnicity,
stdm.student_gender,
sum(case when stdm.student_ethnicity in ('asian') then 1 else 0 end) as [Asian],
sum(case when stdm.student_ethnicity in ('black') then 1 else 0 end) as [Black],
sum(case when stdm.student_ethnicity in ('hispanic') then 1 else 0 end) as [Hispanic],
sum(case when stdm.student_ethnicity in ('american indian') then 1 else 0 end) as [American indian],
sum(case when stdm.student_ethnicity in ('multiracial') then 1 else 0 end) as [Multiracial],
sum(case when stdm.student_ethnicity in ('white') then 1 else 0 end) as [White],
sum(case when stdm.student_gender in ('m') then 1 else 0 end) as [Male],
sum(case when stdm.student_gender in ('f') then 1 else 0 end) as [Female]
from
dw_student.dbo.student stdm
right join
(
select distinct
student_id,intv.intervention_id
from
wain.dbo.meeting mtng
inner join wain.dbo.meeting_intervention mtgi on mtng.meeting_id = mtgi.meeting_id
inner join wain.dbo.intervention intv on mtgi.intervention_id = intv.intervention_id
) subquery
on subquery.student_id = stdm.student_id
group by
stdm.school_region,
stdm.school_number,
stdm.school_name,
subquery.intervention_id,
stdm.student_ethnicity,
subquery.student_id,
stdm.student_gender
order by
stdm.school_region,
stdm.school_number,
stdm.school_name,
stdm.student_ethnicity,
subquery.intervention_id asc,
subquery.student_id,
stdm.student_gender
