June 2, 2015 at 4:42 pm
I am working on a project that was assigned to me that has to do with data in one of our SQL databases. I have the following query that takes information from a single table and averages test scores for each student.
--Group all scores from same student and average them together
with cte_names as
(
SELECT StudentID, MAX(StudentName) AS StudentName
FROM LDCScores
WHERE schoolYear='2014-2015' AND term = 3
GROUP BY StudentID
)
SELECT LDCScores.StudentID
, cte_names.StudentName
, CAST ( AVG ( LDCScores.focus1 ) AS decimal ( 9 , 2 )) AS focusAvg
, CAST ( AVG ( LDCScores.controlling1 ) AS decimal ( 9 , 2 )) AS controllingAvg
, CAST ( AVG ( LDCScores.reading1 ) AS decimal ( 9 , 2 )) AS controllingAvg
, CAST ( AVG ( LDCScores.development1 ) AS decimal ( 9 , 2 )) AS developmentAvg
, CAST ( AVG ( LDCScores.organization1 ) AS decimal ( 9 , 2 )) AS organizationAvg
, CAST ( AVG ( LDCScores.conventions1 ) AS decimal ( 9 , 2 )) AS conventionsAvg
, CAST ( AVG ( LDCScores.content1 ) AS decimal ( 9 , 2 )) AS contentAvg
, LDCScores.studentGradeLevel
, LDCScores.schoolName
FROM
LDCScores INNER JOIN cte_names ON LDCScores.StudentID = cte_names.StudentID
WHERE LDCScores.schoolYear = '2014-2015' AND term = 3
GROUP BY LDCScores.StudentID
, cte_names.StudentName
, LDCScores.studentGradeLevel
, LDCScores.schoolName;
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
I am familiar with SQL, but I think this query goes over my head, so any help that can be provided would be greatly appreciated.
June 2, 2015 at 11:16 pm
Quick question, can you post the DDL for the LDCScores table, some sample data (as an insert statement) and the expected results?
😎
The problem looks relatively straight forward so once you've posted the DDL and the sample data, you should have a solution fairly quickly.
June 3, 2015 at 8:09 am
I think this is what you are asking for:
USE [CurriculumPD]
GO
/****** Object: Table [dbo].[LDCScores] Script Date: 6/3/2015 8:02:38 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[LDCScores](
[studentScoresID] [int] IDENTITY(1,1) NOT NULL,
[studentName] [varchar](50) NULL,
[studentID] [float] NULL,
[studentGradeLevel] [varchar](10) NULL,
[focus1] [float] NULL,
[controlling1] [float] NULL,
[reading1] [float] NULL,
[development1] [float] NULL,
[organization1] [float] NULL,
[conventions1] [float] NULL,
[content1] [float] NULL,
[teachersName] [varchar](50) NULL,
[teacherID] [nchar](10) NULL,
[schoolYear] [varchar](50) NULL,
[term] [nvarchar](50) NULL,
[schoolName] [nchar](50) NULL,
[schoolID] [nchar](10) NULL,
[creationDate] [datetime] NULL CONSTRAINT [DF_LDCScores_created] DEFAULT (getdate()),
CONSTRAINT [PK_StudentScores] PRIMARY KEY CLUSTERED
(
[studentScoresID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE [CurriculumPD]
GO
INSERT INTO [dbo].[LDCScores]
([studentName]
,[studentID]
,[studentGradeLevel]
,[focus1]
,[controlling1]
,[reading1]
,[development1]
,[organization1]
,[conventions1]
,[content1]
,[teachersName]
,[teacherID]
,[schoolYear]
,[term]
,[schoolName]
,[schoolID]
,[creationDate])
VALUES
(<studentName, varchar(50),'Klink, Ash'>
,<studentID, float,5269>
,<studentGradeLevel, varchar(10),12>
,<focus1, float,4>
,<controlling1, float,4>
,<reading1, float,4>
,<development1, float,3.5>
,<organization1, float,3.5>
,<conventions1, float,4>
,<content1, float,3.5>
,<teachersName, varchar(50),>
,<teacherID, nchar(10),>
,<schoolYear, varchar(50),'2014-2015'>
,<term, nvarchar(50),3>
,<schoolName, nchar(50),'Skyline High School'>
,<schoolID, nchar(10),>
,<creationDate, datetime,>)
GO
Please let me know if you need anything else.
June 3, 2015 at 5:19 pm
bsmith 63193 (6/3/2015)
INSERT INTO [dbo].[LDCScores]
([studentName]
,[studentID]
,[studentGradeLevel]
,[focus1]
,[controlling1]
,[reading1]
,[development1]
,[organization1]
,[conventions1]
,[content1]
,[teachersName]
,[teacherID]
,[schoolYear]
,[term]
,[schoolName]
,[schoolID]
,[creationDate])
VALUES
(<studentName, varchar(50),'Klink, Ash'>
,<studentID, float,5269>
,<studentGradeLevel, varchar(10),12>
,<focus1, float,4>
,<controlling1, float,4>
,<reading1, float,4>
,<development1, float,3.5>
,<organization1, float,3.5>
,<conventions1, float,4>
,<content1, float,3.5>
,<teachersName, varchar(50),>
,<teacherID, nchar(10),>
,<schoolYear, varchar(50),'2014-2015'>
,<term, nvarchar(50),3>
,<schoolName, nchar(50),'Skyline High School'>
,<schoolID, nchar(10),>
,<creationDate, datetime,>)
GO[/code]
Please let me know if you need anything else.
I'm not familiar with this form of the insert. It did not work for me on SQL 2012! Getting rid of the the column names in the values clause cleared it up for me.
Having said that, I have a query, but it would be nice to have more than 1 row of data to test against since we are trying to compute averages!
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 3, 2015 at 5:33 pm
Give this a try:
;with cte_Name as
(
Select StudentID,
AVG(Focus1) FocusAvg,
AVG(Controlling1) as ControllingAvg,
AVG(Reading1) as Reading1,
AVG(Development1) as Development1,
AVG(Organization1) as Organization1,
AVG(Conventions1) as Conventions1,
AVG(Content1) as Content1
FROM LDCScores
GROUP BY StudentID
)
Select distinct l.StudentID, l.StudentName, c.FocusAvg, c.ControllingAvg, c.Reading1,
c.Development1, c.Organization1, c.Conventions1, c.Content1,
l.studentGradeLevel, l.SchoolName
From LDCScores l
Inner Join cte_Name c on c.StudentID = l.StudentID
Where l.SchoolYear = '2014-2015'
and l.Term = 3
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2015 at 8:59 am
Looking at the query, I don't see anywhere that is calculating the percentages?
I do not have a sql insert statement for this data as it is being inserted from a 3rd party software. Each row will include information about a student: name, grade, 7 test scores (scores 1-4), teachers name, school, etc.
June 4, 2015 at 9:18 am
bsmith 63193 (6/4/2015)
Looking at the query, I don't see anywhere that is calculating the percentages?
No, there are no percentages. The query I came up with might make it easier to calculate them.
I do not have a sql insert statement for this data as it is being inserted from a 3rd party software. Each row will include information about a student: name, grade, 7 test scores (scores 1-4), teachers name, school, etc.
I now need to take the results from the above query and determine the percentage of students, per school that scored a 2 or greater in grade 7 for each test. For grade 8 scored a 2.5 or greater, grade 9 scored a 3 or greater, grade 10 scored a 3 or greater, grade 11 scored a 3.5 or greater, and grade 12 scored a 3.5 or greater.
Another reason I did not try to do the percentages is because you have a bunch of rules governing the breakdown based on the current grade and scores. Quite frankly, given the size of the row and number of rows that would be needed, I did not feel like generating a bunch of test data. (I have to do my regular job too!) If you would like to get a complete solution, I would suggest providing enough data that can be easily consumed so that a solution can be tested against it.
For information about what to post, I would also suggest reading the link under the signature line.
__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
June 4, 2015 at 9:30 am
Thanks for the feedback I will look over the article you posted a link to and back to you. Thank you for taking a look at this for me.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply