Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2009
    Posts
    2

    Unanswered: Complex query (at least to me)

    I need to write a query that for each course will count grades and last column will calculate average grade for that course (not counting the 1s, only 2,3,4 and 5)

    Tables look like this:

    CREATE TABLE [dbo].[Grades](
    [GradeID] [int] IDENTITY(1,1) NOT NULL,
    [CourseID] [int] NOT NULL,
    [StudentID] [int] NOT NULL,
    [Grade] [int] NOT NULL,
    CONSTRAINT [PK_Grades] PRIMARY KEY CLUSTERED
    (
    [GradeID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
    CONSTRAINT [UC_Grades] UNIQUE NONCLUSTERED
    (
    [StudentID] ASC,
    [CourseID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]

    ALTER TABLE [dbo].[Grades] WITH CHECK ADD CONSTRAINT [FK_Grades_Courses] FOREIGN KEY([CourseID])
    REFERENCES [dbo].[Courses] ([CourseID])


    CREATE TABLE [dbo].[Courses](
    [CourseID] [int] IDENTITY(1,1) NOT NULL,
    [CourseCode] [int] NOT NULL,
    [CourseTitle] [varchar](100) COLLATE Croatian_CI_AS NOT NULL,
    CONSTRAINT [PK_Courses] PRIMARY KEY CLUSTERED
    (
    [CourseID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],



    CREATE TABLE [dbo].[Students](
    [StudentID] [int] IDENTITY(1,1) NOT NULL,
    [LastName] [varchar](50) COLLATE Croatian_CI_AS NOT NULL,
    [FirstName] [varchar](50) COLLATE Croatian_CI_AS NOT NULL,
    CONSTRAINT [PK_Students] PRIMARY KEY CLUSTERED
    (
    [StudentID] ASC
    )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],

    Table data:

    INSERT INTO [Students] (LastName,FirstName) VALUES (McCormack,John)
    go
    INSERT INTO [Students] (LastName,FirstName) VALUES (Donald,Jill)
    go

    INSERT INTO [Courses]([CourseCode],[CourseTitle]) VALUES ('SCI','Science')
    go
    INSERT INTO [Courses]([CourseCode],[CourseTitle]) VALUES ('ENG','English')
    go
    INSERT INTO [Courses]([CourseCode],[CourseTitle]) VALUES ('GEO','Geography')
    go
    INSERT INTO [Courses]([CourseCode],[CourseTitle]) VALUES ('CHE','Chemistry')
    go

    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (1,1,3)
    go
    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (2,1,1)
    go
    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (4,1,4)
    go
    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (2,2,5)
    go
    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (3,2,4)
    go
    INSERT INTO [Grades]([CourseID],[StudentID],[Grade]) VALUES (4,2,3)
    go

    Grade 1 is failure and 5 is highest.

    Expected result:

    Code:
    No. of grades	1	2	3	4	5	AVG
    Science		0	0	1	0	0	3
    English		1	0	0	0	1	5
    Geography	0	0	0	1	0	4
    Chemistry	0	0	1	1	0	3.5
    Thank you!
    Razbol

  2. #2
    Join Date
    Oct 2009
    Posts
    2
    Found solution:

    Code:
    SELECT 
      Courses.CourseTitle,
      sum(CASE WHEN Grades.Grade= 1 THEN 1 ELSE 0 END) AS NoOf1,
      sum(CASE WHEN Grades.Grade= 2 THEN 1 ELSE 0 END) AS NoOf2,
      sum(CASE WHEN Grades.Grade= 3 THEN 1 ELSE 0 END) AS NoOf3,
      sum(CASE WHEN Grades.Grade= 4 THEN 1 ELSE 0 END) AS NoOf4,
      sum(CASE WHEN Grades.Grade= 5 THEN 1 ELSE 0 END) AS NoOf5,
      ISNULL(((CAST(sum(Grades.Grade) AS FLOAT) - sum(CASE WHEN Grades.Grade= 1 THEN 1 ELSE 0 END)) / sum(CASE WHEN Grades.Grade= 1 THEN 0 ELSE 1 END)), 0) AS Average
    FROM
      Courses
      LEFT OUTER JOIN Grades ON (Courses.CourseiID = Grades.CourseID)
    GROUP BY
      Courses.CourseTitle
    Tnx

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'd change your convert to decimal instead of float, but other than that it looks pretty cool to me

    EDIT: Also change IsNull() to Coalesce()
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •