how to remove error in query and use concept of sql query in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

i have one situation
one table is STUDENT_CRpt columns

row_id(identity),
Stu_Id(int),
Stu_Name(varchar),
Class(varchar),
Subject(varchar),
Marks(int)

second table is STUDENT_DTL_Crpt columns:

Stu_Id(int),
Class(varchar),
Gender(varchar),
Address(varchar)

Now in STUDENT_CRpt table 6 rows (raw data)

1   raam    V     Maths      56
2   Sham    V     Maths      59
3   Sham    V     English    49
4   raam    V     English    56
1   geeta   VI    English    60
1   geeta   VI    science    60

According to this in english highest marks are 56 and in maths 59 of V class and of vi class 60

so i tried following query to get output like

1   raam    V   Maths      56   59
2   Sham    V   Maths      59   59
3   Sham    V   English    49   56
4   raam    V   English    56   56
1   geeta   VI   English   60   60
1   geeta   VI   science   60   60

here last column of each row (which is bold) is "highest marks in respective subject of respective class

i hope u got my scenario........
so i used below query

select s.Stu_Name,s.Class,s.Marks,s.Subject ,d.Gender,d.Address,
(select MAX(s.Marks) as 'Highest Marks' from STUDENT_CRpt  group by s.Subject,s.Class)


 from STUDENT_CRpt s,STUDENT_DTL_Crpt d where s.Stu_ID=d.Stu_ID and s.Class=d.Class group by s.Stu_Name,s.Class,d.Address,d.Gender,s.Subject,s.Marks order by Class

i need to tell highest marks too with respective class and subject in each row

but getting an error

Each GROUP BY expression must contain at least one column that is not an outer reference.

i did somewhere wrong ..do tell so that i can get out of this

regards,
thanks

Posted 18-Apr-13 0:38am

shivani 2013

Updated 20-Apr-13 2:17am

Maciej Los

v6

Add a Solution

Comments

Dnyaneshwar Kondbale 18-Apr-13 6:57am

Hey you want list all students marks with their class ,subject and adress,gender.
Or you want Highest mark of student.
Or You want Class,subject,Highest Mark,and Student name and address whose get highest mark
Please clear If I am wrong.

shivani 2013 18-Apr-13 7:08am

i Have 2 classes V and VI with respective 2 subjects english,maths and maths,science.now i want to list all students name with their respective marks opbtained,highest marks(respective class and subject),class,subject,gender and address.
i hope u got it

Dnyaneshwar Kondbale 18-Apr-13 7:29am

Try this .. I thing its help you..

from STUDENT_CRpt s,STUDENT_DTL_Crpt d where s.Stu_ID=d.Stu_ID and s.Class=d.Class
group by s.Stu_Name,s.Class,d.Address,d.Gender,s.Subject,s.Marks order by Class
select s.Stu_Name,s.Class,s.Marks,s.Subject ,d.Gender,d.Address, M.Marks as 'Highest Marks'
from STUDENT_CRpt s,STUDENT_DTL_Crpt d,(select MAX(Marks) as Marks,Subject,Class,Sty_Name from STUDENT_CRpt group by Subject,Class,Sty_Name)
M where s.Stu_ID=d.Stu_ID and s.Class=d.Class and m.Subject=s.Subject and s.Class=m.Class and s.Sty_Name=m.Sty_Name

shivani 2013 18-Apr-13 8:58am

it doesn't worked thanks for ur effort

shivani 2013 20-Apr-13 3:48am

i have updated my question......do see

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2013-04-18T01:01:00

Try this:

SQL

SELECT s.Stu_ID, s.Stu_Name, s.Class, MAX(s.Marks) AS 'Highest marks', s.Subject, d.Gender, d.Address
FROM STUDENT_CRpt AS s INNER JOIN  STUDENT_DTL_Crpt AS d ON s.Stu_ID=d.Stu_ID and s.Class=d.Class
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject, d.Gender,d.Address 
ORDER BY s.Class, s.Stu_Name

[EDIT #1]
Thank you for explanation ;)

To get higest marks in class for subject:

SQL

SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
FROM STUDENT_CRpt AS s
GROUP BY s.Class, s.Subject

To get highest marks for each student in class for subject:

SQL

SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
FROM STUDENT_CRpt AS s
GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject

To get highest marks for each student for subject and for class:

SQL

SELECT t1.*, t2.[Highest marks] AS 'Highest in Class'
FROM (
        --get all students with their marks
	SELECT s.Stu_ID, s.Stu_Name, s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks'
	FROM STUDENT_CRpt AS s
	GROUP BY s.Stu_ID, s.Stu_Name, s.Class, s.Subject
	) AS t1 LEFT JOIN (
                --get highest marks in class
		SELECT s.Class, s.Subject, MAX(s.Marks) AS 'Highest marks', 
		FROM STUDENT_CRpt AS s
		GROUP BY s.Class, s.Subject
		) AS t2 ON t1.Class = t2.Class AND t1.Subject = t2.Subject

Note: Not tested, because i haven't access to SQL Server at this moment.
I hope it help.
[/EDIT]