March 29, 2004 at 3:37 pm
Hello,
I have a query that returns a distinct student grade level, a school name, and the number of teachers that teach that student grade level.
A result sample of the first query looks like this:
| Grade | SchoolName | TeacherCount |
| 00 | Victoriano | 7 |
| 01 | Victoriano | 10 |
There are eight different schools addressed in this query through a CASE statement. The full query is as follows: ***************************************************
Select
SD.Grade,
"School" =
Case
When LC.location2 = 370 Then LC.LocationDesc
When LC.Location2 = 372 Then LC.LocationDesc
When LC.Location2 = 373 Then LC.LocationDesc
When LC.Location2 = 366 Then LC.LocationDesc
When LC.Location2 = 368 Then LC.LocationDesc
When LC.Location2 = 190 Then LC.LocationDesc
When LC.Location2 = 367 Then LC.LocationDesc
When LC.Location2 = 371 Then LC.LocationDesc
End,
"TeacherCount" = Count(distinct TD.TeacherID)
From
teacher_data_Main TD
Inner Join tblLocation LC On TD.SchoolNum = LC.Location2
Inner Join student_data_main SD On TD.TeacherID = SD.TeacherID
Where
SD.Grade In (00,01,02,03,04,05)
and
TD.SystemUserName is not null
Group by SD.Grade, LC.Location2, LC.LocationDesc
Order by LC.LocationDesc
********************************************************
I need to have another representation of this data so that the query will return the data in this format as well:
| School | GK | G1 | G2 | G3 | G4 | G5 |
| Victoriano | 7 | 10 | 14 | 10 | 12 | 9 |
I attempted to accompish the above layout with the following code:
************************************************
Select
Distinct LC.LocationDesc AS School,
Case When SD.Grade = 00 Then Count(distinct TD.TeacherID)End as GK,
Case When SD.Grade = 01 Then Count(distinct TD.TeacherID)End as G1,
Case When SD.Grade = 02 Then Count(distinct TD.TeacherID)End as G2,
Case When SD.Grade = 03 Then Count(distinct TD.TeacherID)End as G3,
Case When SD.Grade = 04 Then Count(distinct TD.TeacherID)End as G4,
Case When SD.Grade = 05 Then Count(distinct TD.TeacherID)End as G5
From
teacher_data_Main TD
Inner Join tblLocation LC On TD.SchoolNum = LC.Location2
Inner Join student_data_main SD On TD.TeacherID = SD.TeacherID
Where
SD.Grade In (00,01,02,03,04,05)
and
TD.SystemUserName is not null
Group by LC.LocationDesc, SD.Grade
Order by LC.LocationDesc
*******************************************
A sample of the results from the above code came out like this:
| School | GK | G1 | G2 | G3 | G4 | G5 |
| Victoriano | NULL | NULL | NULL | NULL | NULL | 9 |
| Victoriano | NULL | NULL | NULL | NULL | 12 | NULL |
| Victoriano | NULL | NULL | NULL | 10 | NULL | NULL |
| Victoriano | NULL | NULL | 14 | NULL | NULL | NULL |
| Victoriano | NULL | 10 | NULL | NULL | NULL | NULL |
| Victoriano | 7 | NULL | NULL | NULL | NULL | NULL |
Of course, this was not the result I was looking for.
What changes to I need to make to the second code segment in order to achieve a result set like this?:
| School | GK | G1 | G2 | G3 | G4 | G5 |
| Victoriano | 7 | 10 | 14 | 10 | 12 | 9 |
Thanks for your help!
CSDunn
March 29, 2004 at 6:48 pm
Right off the only way I see is to do your query as a sub select and an other selection with SUM.
Select
School,
Sum(GK) GK,
Sum(G1) G1,
Sum(G2) G2,
Sum(G3) G3,
Sum(G4) G4,
Sum(G5) G5,
(
Select
Distinct LC.LocationDesc AS School,
Case When SD.Grade = 00 Then Count(distinct TD.TeacherID)End as GK,
Case When SD.Grade = 01 Then Count(distinct TD.TeacherID)End as G1,
Case When SD.Grade = 02 Then Count(distinct TD.TeacherID)End as G2,
Case When SD.Grade = 03 Then Count(distinct TD.TeacherID)End as G3,
Case When SD.Grade = 04 Then Count(distinct TD.TeacherID)End as G4,
Case When SD.Grade = 05 Then Count(distinct TD.TeacherID)End as G5
From
teacher_data_Main TD
Inner Join tblLocation LC On TD.SchoolNum = LC.Location2
Inner Join student_data_main SD On TD.TeacherID = SD.TeacherID
Where
SD.Grade In (00,01,02,03,04,05)
and
TD.SystemUserName is not null
Group by LC.LocationDesc, SD.Grade
Order by LC.LocationDesc
) AS BaseTbl
GROUP BY School
March 30, 2004 at 9:46 am
-- first query
SELECT SD.Grade, LC.LocationDesc AS 'SchoolName', COUNT(TD.TeacherID) AS 'TeacherCount'
FROM teacher_data_Main TD INNER JOIN tblLocation LC ON TD.SchoolNum = LC.Location2
INNER JOIN student_data_main SD ON TD.TeacherID = SD.TeacherID
WHERE TD.SchoolNum IN (190,366,367,368,370,371,372,373)
AND LEN(TD.SystemUserName) > 0
AND SD.Grade In (00,01,02,03,04,05)
GROUP BY SD.Grade, LC.LocationDesc
ORDER BY LC.LocationDesc ASC
-- second query
SELECT 'School' = LC.LocationDesc,
SUM(CASE WHEN SD.Grade = 00 THEN 1 ELSE 0 END) AS 'GK',
SUM(CASE WHEN SD.Grade = 01 THEN 1 ELSE 0 END) AS 'G1',
SUM(CASE WHEN SD.Grade = 02 THEN 1 ELSE 0 END) AS 'G2',
SUM(CASE WHEN SD.Grade = 03 THEN 1 ELSE 0 END) AS 'G3',
SUM(CASE WHEN SD.Grade = 04 THEN 1 ELSE 0 END) AS 'G4',
SUM(CASE WHEN SD.Grade = 05 THEN 1 ELSE 0 END) AS 'G5'
FROM teacher_data_Main TD INNER JOIN tblLocation LC ON TD.SchoolNum = LC.Location2
INNER JOIN student_data_main SD ON TD.TeacherID = SD.TeacherID
WHERE LEN(TD.SystemUserName) > 0
AND SD.Grade In (00,01,02,03,04,05)
GROUP BY LC.LocationDesc
ORDER BY LC.LocationDesc ASC
March 30, 2004 at 11:35 am
Thank you all for your help!
CSDunn
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply