How to select max marks from one subject and min marks from another subject in a student table?
how to get name and marks of top three students using sql
sql select max id + 1
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 ?
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');
CHAPTER 15: MORE ON SOL-GROUPING RECORDS AND TABLE JOINS SQL Function: These function operates on group of rows instead of single row that is why group functions are as follows: TABLE:Student Rollino Sname Subject Marks in a Mysqli-> - Select max(marks)|96 specified column from Student; MIN() Can you help me in getting query to retrieve STUD_NAME, SUBJECT, MAX (MARKS) . I having tried below but facing issue in getting STUD_NAME. select SUBJECT, MAX(MARKS) as Highest from MARKS inner join STUDENT ON STUDENT.STUD_ID = MARKS.STUD_ID GROUP BY SUBJECT. sql-server-2008 t-sql.
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;
Directions (0.341-345) Study the following table carefully and answer the in Different Subjects Subjects (Maximum Marks) Students|Hindi English Maths S.St. Science Marathi Phy.Edu How many marks did E get in all the subjects together? How many students have scored the highest marks in more than one subject? More on Average ( AVG ) Query. Maximum mark of each student in all exams SELECT id,name,class,sex,student_id,mark FROM `student6` a LEFT JOIN (SELECT student_id, MAX(mark) as mark FROM student_mark GROUP BY student_id ) b on a.id= b.student_id
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
Same way we can get the minimum value of a range of records by using SQL MIN This will display one record with all details of the highest mark. SELECT id,name,class,MAX(mark) as max_mark FROM `student` We have to use Group By clause if we ask for the query to return any other field name other than the max. For students whose grades are lower than 8, we will output ‘NULL’ as their name and for ‘NULL’ with same grade, we order by marks in ascending order, so we order by marks finally: 1 SELECT IF (g.Grade< 8 , NULL , s.Name), g.Grade, s.Marks FROM Students AS s JOIN Grades AS g ON s.Marks BETWEEN g.Min_Mark AND g.Max_Mark ORDER BY g.Grade DESC , s.Name, s.Marks;
WHERE with your condition
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
No wonder, Oswaal Books holds an enviable place in every student's heart! (d) On which day were the minimum number of shirts sold ? (e) How (b) Name the subject, in which Aziz scored maximum marks. Thus, we get the following bar graph. Which activity is preferred by most of the students other than playing ? • Put them in condition statement and get the grading and percentage of students. • The Student who get clear the required condition for Division placement. Here is the C program to Find the Percentage or marks of students. This C program is an example to use Control statements. #include<stdio.h> int m1,m2,m3,total; printf ("Enter 3 Nos.");
For each student, we have collected information on 11 subjects' marks*: Table 1. Descriptive statistics of entry data Program No. of students Subjects Average Students usually choose to study one of the two major programs, IT with 38% and standard deviation, maximum and minimum values of the efficiency index by Answer / sanjay. create table test(id int identity,marks int) insert into test select 20 union all select 31 union all select 33 union all select 1 union all select 3
The bar chart also represents the percentage marks obtained by A and B in three subjects. Each subject carries 100 marks. Students always get integral marks SELECT * FROM `student` WHERE subject='Science' GROUP BY marks ORDER BY marks DESC LIMIT 0,3. This will return three rows of the students, with top three distinct marks. Please note that the other information (maybe id, name etc) will correspond to the first student having such marks, as returned by the query.
First one is Student table having STUD_ID , STUD_NAME & Second one is MARKS table having STUD_ID, STUD_ID] AND rn=1 Another method would be to use DENSE_RANK() and CROSS APPLY . select stud_name as [Student Name] , [subject] as [Subject] , marks as [Max Marks] from dbo. select subject_name,student_name from student inner join mark m using(student_id) inner join subject su using(subject_id) inner join (select subject_id,max(value) as maximum from mark m group by subject_id) highestmark using(subject_id) where highestmark.maximum = m.value order by subject_name,student_name; share.
- Thanks. I didn't know about case-when. I want to learn advance sql can you share any resource ?
- @JeetashRathi. . . It is
caseexpression Look at docs.microsoft.com/en-us/sql/t-sql/language-elements/…