T-SQL Query | [ The Average Marks Puzzle ]

Puzzle Statement

1. The puzzle is simple.
2. You have to list out student information where the student scored more than the Average Marks per subject.
3. Please check out the sample input and expected output for details.

Sample Input

Expected Output

Rules/Restrictions

• The solution should be should use “SELECT” statement or “CTE”.
• Add your solution(s) in the comments section or send you solution(s) to pawankkmr@gmail.com

Script

Use the below script to generate the source table and fill them up with the sample data.

CREATE TABLE Neeraj
(
       Sname VARCHAR(1)
       ,SMarks INT
       ,SSubject VARCHAR(1) 
)
GO

INSERT INTO Neeraj(Sname, SMarks , SSubject) VALUES 
('A'   ,       10   ,  'X'),
('B'   ,         20  , 'X'),
('C'   ,         30  ,  'Y'),
('D',     40  ,  'Y')

Update May 10 | Solutions — Pawan Kumar Khowal

--

/************   SOLUTION 1         ****************/

SELECT n.*, Ag FROM Neeraj n
INNER JOIN (
SELECT AVG(SMarks) Ag , SSubject FROM Neeraj GROUP BY SSubject ) a on a.SSubject = n.SSubject
WHERE SMarks > Ag



/************   SOLUTION 2        ****************/

SELECT * 
FROM Neeraj n 
WHERE SMarks > ( SELECT AVG(SMarks) ag FROM Neeraj n1 WHERE n1.SSubject = n.SSubject GROUP BY SSubject ) 


--

Add a comment if you have any other solution in mind. We all need to learn.

Keep Learning

Http://MSBISkills.com