December 29, 2013 at 5:18 am
I have a query where I am trying to get a total Student Count by Teacher, and also get a Count of students based on the selected Score. I can get the student count to match when I select one score but when I change the score that I am looking for it also changes the student count and the overall student count should remain the same. i.e. a teacher has a total of 158 students in all of their classes. When the score is set to an f the student count stays at 158 and I get a correct score count of 8, but when I change the score being looked for to a d then my total student count goes to 52 which should remain at 158 but I get the correct count of scores which is 3.
How can I do this that the total count stays consistent regardless of the score variable.
Here is what I have.
DECLARE @School int
DECLARE @Term int
DECLARE @Score varchar(2)
DECLARE @Task int
SET @School = 109
SET @Term = 517
SET @Task = 2
SET @Score = 'd'
SELECT ca.name AS School,i.LastName+','+ i.FirstName AS Teacher, gt.name AS Task, SUM(sr.studentcount) AS [Total Students],
t.name AS Term, gs.score, COUNT(score) AS Total
FROM ScoreTable gs
JOIN table1 se ON se.sectionID = gs.sectionid
JOIN Tabele2 I on I.personID = se.teacherPersonID
JOIN Table3 sr ON sr.sectionID = gs.sectionid
JOIN Table4 gt ON gt.taskID = gs.taskid
JOIN table5 ca ON ca.calendarID = gs.calendarID
JOIN table6 t ON t.termID = gs.termID
JOIN table7 ts ON ts.termScheduleID = t.termScheduleID
JOIN table8 ss ON ss.structureID = ts.structureID
where ca.v1 = (@School) and (t.v2 IN(@Term) and gs.v3 IN (@Task) and gs.v4 IN(@Score) )
GROUP by ca.name,t.name, gt.name, i.Name, i.Name, gt.name,gs.score
ORDER By [Teacher]
December 29, 2013 at 5:41 am
I am not able to understand your requirement...
We will be able to help you if you post the table structure with some sample data and expected output
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 29, 2013 at 6:31 am
Here is the actual script with the actual table names. Below the script is the result based on what was entered for the Score Variable.
DECLARE @School int
DECLARE @Term int
DECLARE @Score varchar(2)
DECLARE @Task int
SET @School = 109
SET @Term = 517
SET @Task = 2
SET @Score = 'f'
SELECT TOP 5 ca.name AS School,i.LastName+','+ i.FirstName AS Teacher, gt.name AS Task, SUM(sr.studentcount) AS [Total Students],
t.name AS Term, gs.score, COUNT(score) AS Total
FROM GradingScore gs
JOIN Section se ON se.sectionID = gs.sectionid
JOIN individual I on I.personID = se.teacherPersonID
JOIN v_SectionRosterSummary sr ON sr.sectionID = gs.sectionid
JOIN GradingTask gt ON gt.taskID = gs.taskid
JOIN calendar ca ON ca.calendarID = gs.calendarID
JOIN Term t ON t.termID = gs.termID
JOIN TermSchedule ts ON ts.termScheduleID = t.termScheduleID
JOIN ScheduleStructure ss ON ss.structureID = ts.structureID
where ca.calendarid = (@School) and (t.termID IN(@Term) and gs.taskid IN (@Task) and gs.score IN(@Score) )
GROUP by ca.name,t.name, gt.name, i.lastName, i.firstName, gt.name,gs.score
ORDER By [Teacher]
Using the score of D in the score parameter I get this. Smith, Nicole has a total of 158 students in all of her classes which it should still say
instead of 52. The 3 in the total column is correct though but it should be showing 3 out of 158 that recieved a D instead of 3 out of 52
School Teacher Task Total StudentsTermscoreTotal
13-14 KHSSmith, NicoleQuarter Grade52 Q1 D 3
13-14 KHSTrevor, StacyQuarter Grade88 Q1 D 7
Using the score of F in the score parameter I get this which is correct. Smith, Nicole has a total of 158 students in all of her classes
and she has a total of 8 out of the 158 that recieved and F
School Teacher Task Total StudentsTermscoreTotal
13-14 KHSSmith,NicoleQuarter Grade158 Q1 F 8
13-14 KHSTrevor,StacyQuarter Grade24 Q1 F 2
Hope this helps and Thanks in advance
December 29, 2013 at 6:41 am
It will be good if you post table structure with some sample data and desired output...
Plesse follow link in my signature...
_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
December 30, 2013 at 9:19 am
I've attached a document with the table structures for the tables being used.
What I am needing is that the Total Student count remain the same for that teacher regardless of which score is being selected and that I get a count of those students that have that score based on the total number of students that teacher teaches.
Thanks in Advance
December 30, 2013 at 9:44 am
Wayne, it may look like your query is close, but I'd still recommend starting again. Here's a good point from which to start:
DECLARE @School int, @Term int, @Score varchar(2), @Task int
SELECT @School = 109, @Term = 517, @Task = 2, @Score = 'f'
SELECT gs.*
FROM GradingScore gs
WHERE gs.calendarID = @School
AND gs.termID = @Term
AND gs.taskid = @Task
AND gs.score = @Score
Figure out what you might expect to see from this.
You know that your lookups are 1 to 1 and won't change your rowcount, so leave them out for now. Add in the remaining tables one at a time and measure the effect on the rowcount - start with Section, then Individual, then v_SectionRosterSummary. It's likely that you will have to obtain aggregates of at least one of these as a subquery before joining to the rest of the query.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2013 at 9:56 am
Thanks. I've tried that to make sure my numbers are correct and I can get the numbers to pull correctly even filtering down to one section and get my numbers to be correct using the aggregates, when I pull based on a score of F I get 158 total students which is correct for my example teacher and I get a total of 8 who received and F which is also correct, so my Total Student counts and Totals are correct when I pull based on that score. The problem is that when I change the score to a D then I still get the correct number of students 3 that actually got that score but for some reason my Total students is changing from 158 to 32 when that number should remain the same as the teacher still has the same number of students
December 30, 2013 at 10:07 am
WayneHess (12/30/2013)
Thanks. I've tried that to make sure my numbers are correct and I can get the numbers to pull correctly even filtering down to one section and get my numbers to be correct using the aggregates, when I pull based on a score of F I get 158 total students which is correct for my example teacher and I get a total of 8 who received and F which is also correct, so my Total Student counts and Totals are correct when I pull based on that score. The problem is that when I change the score to a D then I still get the correct number of students 3 that actually got that score but for some reason my Total students is changing from 158 to 32 when that number should remain the same as the teacher still has the same number of students
Then you need to look at the data, yes? Start with the simple query I posted, add in v_SectionRosterSummary and look at studentcount, for different values of Score.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
December 30, 2013 at 10:20 am
Thanks I'll try that and see what happens.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply