CODEWITHSUNDEEP
sqlsql-server
Jeetash Rathi
Jeetash Rathi

Reputation: 3

How to select max marks from one subject and min marks from another subject in a student table?

Select max marks from math subject and select min marks from physics subject.

I can only think of it like following:

Select subject, max(Marks) as Max_Marks, MIN(Marks) as Min_Marks
from Student 
group by Subject

But above query shows max, min for each subject.

Any other way to query this ?

Upvotes: 0

Views: 7755

Answers (4)

wa56
wa56

Reputation: 341

Above three answers are correct. This way may be easier understand

SELECT 
  (SELECT max(Marks) from Student WHERE subject = 'math')    AS Max_Math
, (SELECT min(Marks) from Student WHERE subject = 'physics') AS Min_Physics;

Upvotes: 1

Dhana
Dhana

Reputation: 1658

Add WHERE with your condition

Query:

CREATE TABLE #temp(SUBJECT varchar(12), MARK int)
INSERT INTO #temp(SUBJECT, MARK)
select 'Maths',   100  union
select 'Maths',   90  union
select 'Maths',   40  union
select 'Physics',   60  union
select 'Physics',   30  union
select 'Physics',   20  

Select subject, max(Mark) as Max_Marks, MIN(Mark) as Min_Marks
from #temp 
where Subject in ('maths','physics')
group by Subject

Output:

enter image description here

Upvotes: 0

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

Just include WHERE clause & do conditional aggregation :

select max(case when Subject = 'math' then Marks end) as math_Max_Marks,
       min(case when Subject = 'physics' then Marks end) as physics_Min_Marks
from Student 
where Subject in ('math', 'physics');

Upvotes: 1

Eray Balkanli
Eray Balkanli

Reputation: 7960

You can use a query like below, with a where clause and use a case-when statement to decide whether you need to use max or min function.

Select subject, mark = (case when subject = 'math' then max(Marks) else min(Marks) end)
from Student 
where Subject = 'math'
   or Subject = 'physics'
group by Subject

Upvotes: 0

Related Questions