Stored procedure - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Stored procedure

Author

Topic

Bhagavatula
Starting Member

1 Post

Posted - 2009-02-18 : 05:58:01

hi

i have student marks table which has student name, group and individual subjects' marks.

what i need is

1.total of all subject marks for each student
2.display the total marks >75 in distinction column
3.and others in fail column.

the student name, number of students who has got distinction and failed will be displayed in separate table.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-18 : 06:11:16

have you given this a try ?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-18 : 06:26:34

[code]try this i guess it check it
here some sample data
DECLARE @tab TABLE(studentname VARCHAR(32), groupname VARCHAR(32), m1 INT, m2 INT,m3 INT)
INSERT INTO @tab SELECT 'bklr','bt',98,89,92
INSERT INTO @tab SELECT 'adi','eee',54,87,67
INSERT INTO @tab SELECT 'raju','cse',65,45,40
INSERT INTO @tab SELECT 'adi','eee',54,87,67
INSERT INTO @tab SELECT 'ramu','eie',89,78,73

SELECT studentname, count(DISTINCT studentname)
,CASE WHEN (m1+m2+m3) > 225 THEN 'Distinction' END AS Distinction
FROM @tab GROUP BY studentname,CASE WHEN (m1+m2+m3) > 225 THEN 'Distinction' END

SELECT studentname, count(DISTINCT studentname)
,CASE WHEN (m1+m2+m3) < 225 THEN 'Failed' END AS Distinction
FROM @tab GROUP BY studentname,CASE WHEN (m1+m2+m3) < 225 THEN 'Failed' END
[/code]

ke.neelima
Starting Member

6 Posts

Posted - 2009-02-18 : 06:28:35

select name,sum(marks) [total],
case when sum(marks) >= 75 then sum(marks)
end [distinction],
case when sum(marks) < 75 then sum(marks)
end [fail]
from emp
group by name

May be u need to check 'percentage >= 75' instead of sum..
Check once :)

Regards,

Neelima

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources