• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 256
  • Last Modified:

Help with Designing SQL Server View and computing the Aggregrate column values

I have been tasked to create a application that display a view of the student's score cards

The format is out put is attached in the file >> Scorecardview.png
This view you see in the Scorecardview combines 3 tables , i am attaching the same for your reference.

All works fine as desired, Now i have been asked to expand the view to compute the class averages and automatically rank the students based on the mark secured.

And i wish to have this value also stored in the view columns.

I need help with adding 2 column to the view and generating the values for the respective columns, ie the CLASS_AVERAGE AND THE RANK based on the mark secured for each subject

I have enclosed the script of the table and the view,
CREATE TABLE [dbo].[SIS_COURSES_REGISTRATION](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[COURSE_ID] [int] NULL,
	[COURSE_NAME] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CREATE_DATE] [datetime] NULL CONSTRAINT [DF_SIS_COURSES_REGISTRATION_CREATE_DATE]  DEFAULT (getdate()),
	[UPDATE_DATE] [datetime] NULL,
 CONSTRAINT [PK_SIS_COURSES_REGISTRATION] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
 
 
/****** Object:  Table [dbo].[SIS_KEYWORD_EXAM_CATEGORY]    Script Date: 02/18/2009 11:05:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[SIS_KEYWORD_EXAM_CATEGORY](
	[KEYWORD_ID] [int] IDENTITY(1,1) NOT NULL,
	[NAME] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[DESCRIPTION] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[SORT_ORDER] [int] NULL CONSTRAINT [DF_KEYWORD_EXAM_CATEGORY_SORT_ORDER]  DEFAULT ((0)),
	[KEYWORD_CATEGORY_ID] [int] NULL CONSTRAINT [DF_KEYWORD_EXAM_CATEGORY_KEYWORD_CATEGORY_ID]  DEFAULT ((-1)),
	[PARENT_KEYWORD_ID] [int] NULL CONSTRAINT [DF_KEYWORD_EXAM_CATEGORY_PARENT_KEYWORD_ID]  DEFAULT ((-1)),
	[DISPLAY_STATUS_ID] [int] NULL CONSTRAINT [DF_KEYWORD_EXAM_CATEGORY_DISPLAY_STATUS_ID]  DEFAULT ((1)),
 CONSTRAINT [PK_KEYWORD_EXAM_CATEGORY] PRIMARY KEY CLUSTERED 
(
	[KEYWORD_ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
 
/****** Object:  Table [dbo].[SIS_STUDENT_EXAM_SCORECARD]    Script Date: 02/18/2009 11:06:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[SIS_STUDENT_EXAM_SCORECARD](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[STUDENT_ID] [int] NULL CONSTRAINT [DF_SIS_STUDENT_EXAM_SCORECARD_STUDENT_ID]  DEFAULT ((-1)),
	[COURSE_ID] [int] NULL,
	[EXAM_CATEGORY_ID] [int] NULL CONSTRAINT [DF_SIS_STUDENT_EXAM_SCORECARD_EXAM_CATEGORY_ID]  DEFAULT ((-1)),
	[MARKS_SECURED] [int] NULL CONSTRAINT [DF_SIS_STUDENT_EXAM_SCORECARD_MARKS_SECURED]  DEFAULT ((0)),
	[CREATE_DATE] [datetime] NULL CONSTRAINT [DF_SIS_STUDENT_EXAM_SCORECARD_CREATE_DATE]  DEFAULT (getdate()),
	[UPDATE_DATE] [datetime] NULL,
 CONSTRAINT [PK_SIS_STUDENT_EXAM_SCORECARD] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
 
 
/****** Object:  View [dbo].[SIS_STUDENT_EXAM_SCORECARD_VIEW]    Script Date: 02/18/2009 11:06:48 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE VIEW [dbo].[SIS_STUDENT_EXAM_SCORECARD_VIEW]
 
AS
SELECT
	SSES.ID,
	SSES.STUDENT_ID,
	ISNULL(AUR.FULL_NAME,'') AS STUDENT_NAME,
	SSES.COURSE_ID,
	SCR.COURSE_NAME,
	SSES.EXAM_CATEGORY_ID,
	ISNULL(KEC.NAME,'') AS EXAM_CATEGORY,
	SSES.MARKS_SECURED,
	ISNULL(CONVERT(varchar, SSES.CREATE_DATE, 106), '') AS CREATE_DATE, 
	ISNULL(CONVERT(varchar, SSES.UPDATE_DATE, 106), '') AS UPDATE_DATE 
 
	
FROM
	SIS_STUDENT_EXAM_SCORECARD SSES  LEFT OUTER JOIN
	SIS_KEYWORD_EXAM_CATEGORY KEC ON SSES.EXAM_CATEGORY_ID=KEC.KEYWORD_ID LEFT OUTER JOIN
	SIS_COURSES_REGISTRATION SCR ON SSES.COURSE_ID=SCR.COURSE_ID  LEFT OUTER JOIN
	ACCOUNT_USER_REGISTRATION_VIEW AUR ON SSES.STUDENT_ID=AUR.ID

Open in new window

Scorecardview.png
scorecardtable.png
coursetable.png
examcategorytable.png
0
TECH_NET
Asked:
TECH_NET
1 Solution
 
TECH_NETAuthor Commented:
I calculate the average of marks secured for course for 'English' and for the Final Exam  using the following script
SELECT AVG(MARKS_SECURED) FROM SIS_STUDENT_EXAM_SCORECARD
WHERE
      COURSE_ID=1 AND
      EXAM_CATEGORY_ID=1
0
 
Ted BouskillSenior Software DeveloperCommented:
Why are you using LEFT joins?  Can a scorecard exist without a record in exam categories, course registration and user registration?  If no, then you should be using INNER JOIN's

SELECT
        SSES.ID,
        SSES.STUDENT_ID,
        ISNULL(AUR.FULL_NAME,'') AS STUDENT_NAME,
        SSES.COURSE_ID,
        SCR.COURSE_NAME,
        SSES.EXAM_CATEGORY_ID,
        ISNULL(KEC.NAME,'') AS EXAM_CATEGORY,
        ISNULL(CONVERT(varchar, SSES.CREATE_DATE, 106), '') AS CREATE_DATE, 
        ISNULL(CONVERT(varchar, SSES.UPDATE_DATE, 106), '') AS UPDATE_DATE 
FROM
(
    SELECT AVG(MARKS_SECURED) AvgMarks, STUDENT_ID, COURSE_ID, EXAM_CATEGORY_ID FROM SIS_STUDENT_EXAM_SCORECARD GROUP BY STUDENT_ID, COURSE_ID, EXAM_CATEGORY_ID
) AvgScores
INNER JOIN SIS_STUDENT_EXAM_SCORECARD SSES
        ON SSES.STUDENT_ID = AvgScores.STUDENT_ID AND SSES.COURSE_ID = AvgScores.COURSE_ID AND SSES.EXAM_CATEGORY_ID = AvgScores.EXAM_CATEGORY_ID
    INNER JOIN SIS_KEYWORD_EXAM_CATEGORY KEC ON SSES.EXAM_CATEGORY_ID=KEC.KEYWORD_ID
    INNER JOIN SIS_COURSES_REGISTRATION SCR ON SSES.COURSE_ID=SCR.COURSE_ID
    INNER JOIN ACCOUNT_USER_REGISTRATION_VIEW AUR ON SSES.STUDENT_ID=AUR.ID

Open in new window

0
 
SharathData EngineerCommented:
Is this home work? The data seems like so....
If not provide the definition of ACCOUNT_USER_REGISTRATION_VIEW and sample data of this view.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Mark WillsTopic AdvisorCommented:
Have a look at and can check once you have some test data for us, or, can run it on your database using just the select bit onwards....
CREATE VIEW [dbo].[SIS_STUDENT_EXAM_SCORECARD_VIEW]
 
AS
SELECT
    rank() over (partition by SSES.COURSE_ID order by SSES.MARKS_SECURED desc) as STUDENT_RANK,
    (SELECT AVG(MARKS_SECURED) FROM SIS_STUDENT_EXAM_SCORECARD A WHERE A.COURSE_ID=SSES.COURSE_ID and A.EXAM_CATEGORY_ID=SSES.EXAM_CATEGORY_ID) as AVG_MARK,
	SSES.ID,
	SSES.STUDENT_ID,
	ISNULL(AUR.FULL_NAME,'') AS STUDENT_NAME,
	SSES.COURSE_ID,
	SCR.COURSE_NAME,
	SSES.EXAM_CATEGORY_ID,
	ISNULL(KEC.NAME,'') AS EXAM_CATEGORY,
	SSES.MARKS_SECURED,
	ISNULL(CONVERT(varchar, SSES.CREATE_DATE, 106), '') AS CREATE_DATE, 
	ISNULL(CONVERT(varchar, SSES.UPDATE_DATE, 106), '') AS UPDATE_DATE 
	
FROM
	SIS_STUDENT_EXAM_SCORECARD SSES  LEFT OUTER JOIN
	SIS_KEYWORD_EXAM_CATEGORY KEC ON SSES.EXAM_CATEGORY_ID=KEC.KEYWORD_ID LEFT OUTER JOIN
	SIS_COURSES_REGISTRATION SCR ON SSES.COURSE_ID=SCR.COURSE_ID  LEFT OUTER JOIN
	ACCOUNT_USER_REGISTRATION_VIEW AUR ON SSES.STUDENT_ID=AUR.ID

Open in new window

0
 
TECH_NETAuthor Commented:
mark_wills, how do i format the average to be 2 decimals only,
0
 
TECH_NETAuthor Commented:
mark_wills,
 
Your scripts work fine except for one condition. The average marks and class rank should be unique
for each class,(ie CLASS_ID) , subject (SUBJECT_ID) and Exam Category (EXAM_CATEGORY_ID)

It fails for different CLASS_ID.

Can you please modifiy the script for the same,

0
 
Mark WillsTopic AdvisorCommented:
use convert or cast into a decimal (18,2) or can use round... e.g.

    (SELECT round(AVG(MARKS_SECURED),2) FROM SIS_STUDENT_EXAM_SCORECARD A WHERE A.COURSE_ID=SSES.COURSE_ID and A.EXAM_CATEGORY_ID=SSES.EXAM_CATEGORY_ID) as AVG_MARK,


0
 
TECH_NETAuthor Commented:
I added the following script to the condition

rank() over (partition by SSES.COURSE_ID order by SSES.MARKS_SECURED desc) as STUDENT_RANK,
    (SELECT AVG(MARKS_SECURED) FROM SIS_STUDENT_EXAM_SCORECARD A WHERE A.COURSE_ID=SSES.COURSE_ID and A.CLASS_ID=SSES.CLASS_ID
and A.EXAM_CATEGORY_ID=SSES.EXAM_CATEGORY_ID) as AVG_MARK,

but the ranking is broken.
0
 
Mark WillsTopic AdvisorCommented:
Ummm, where is class_id - it is not in any of the scripts you have supplied... Taken it as being in SSES table alias... You might need to modify accordingly.
CREATE VIEW [dbo].[SIS_STUDENT_EXAM_SCORECARD_VIEW]
 
AS
SELECT
    rank() over (partition by SSES.COURSE_ID, SSES.CLASS_ID, SSES.EXAM_CATEGORY_ID order by SSES.MARKS_SECURED desc) as STUDENT_RANK,
    (SELECT round(AVG(MARKS_SECURED),2) FROM SIS_STUDENT_EXAM_SCORECARD A WHERE A.COURSE_ID=SSES.COURSE_ID and A.CLASS_ID=SSES.CLASS_ID and A.EXAM_CATEGORY_ID=SSES.EXAM_CATEGORY_ID) as AVG_MARK,
	SSES.ID,
	SSES.STUDENT_ID,
	ISNULL(AUR.FULL_NAME,'') AS STUDENT_NAME,
	SSES.COURSE_ID,
	SCR.COURSE_NAME,
	SSES.EXAM_CATEGORY_ID,
	ISNULL(KEC.NAME,'') AS EXAM_CATEGORY,
	SSES.MARKS_SECURED,
	ISNULL(CONVERT(varchar, SSES.CREATE_DATE, 106), '') AS CREATE_DATE, 
	ISNULL(CONVERT(varchar, SSES.UPDATE_DATE, 106), '') AS UPDATE_DATE 
	
FROM
	SIS_STUDENT_EXAM_SCORECARD SSES  LEFT OUTER JOIN
	SIS_KEYWORD_EXAM_CATEGORY KEC ON SSES.EXAM_CATEGORY_ID=KEC.KEYWORD_ID LEFT OUTER JOIN
	SIS_COURSES_REGISTRATION SCR ON SSES.COURSE_ID=SCR.COURSE_ID  LEFT OUTER JOIN
	ACCOUNT_USER_REGISTRATION_VIEW AUR ON SSES.STUDENT_ID=AUR.ID

Open in new window

0
 
TECH_NETAuthor Commented:
I got the averege column fixed to 2 decimals by using CONVERT function.

I have added  the following script to the existing condition
and A.CLASS_ID=SSES.CLASS_ID

and it fixes the Averages for different classes but the ranking is not correctly been returned.
0
 
Mark WillsTopic AdvisorCommented:
OK, so did you see the additional columns in the PARTITION BY clause ?

It should be restarting the ranking for each combination in the partition by (ie SSES.COURSE_ID, SSES.CLASS_ID, SSES.EXAM_CATEGORY_ID), starting at 1 for the highest mark...

What are you seeing ? Can you post your results and your query ?
0
 
TECH_NETAuthor Commented:
Can you explain what PARTITION BY clauses does.
Thanks
0
 
Mark WillsTopic AdvisorCommented:
it is like a "group by" or more accurately a control break which resets the counter (in this case, that counter is the RANK() function)
0
 
TECH_NETAuthor Commented:
Thanks for the quick responses.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now