Wick Wick - 6 months ago 9
SQL Question

Confusing with Having query in sql

I am using sql server management studio 2012 and have to make a query to show which subject a student has failed(condition for failing is point<5.0) the most for the first time from this table

StudentID | SubjectID | First/Second_Time | Point.
1 | 02 | 1 | 5.0
2 | 04 | 2 | 7.0
3 | 03 | 2 | 9
... etc


Here are my teacher's query:

SELECT SubjectID
FROM Result(NAME OF the TABLE)
WHERE [First/Second_Time] = 1 AND Point < 5
GROUP BY SubjectID
HAVING count(point) >= ALL
(
SELECT count(point)
FROM Result
WHERE [First/Second_Time] = 1 AND point < 5
GROUP BY SubjectID
)


I don't understand the reason for making the having query. Because
Count(point)
is always
>=all(select count(point)
from Result
where First/Second_Time=1 and point<5
group by SubjectID)
, isnt it ?
and it doesn't show that the subject has most student fail for the first time. Thanks in advance and sorry for my bad english

APH APH
Answer

The subquery is returning a list of the number of times a subject was failed (on the first attempt). It might be easier for you to see what it's doing if you run it like this:

SELECT SubjectID, count(point)
    FROM Result
    WHERE [First/Second_Time] = 1 AND point < 5
    GROUP BY SubjectID

So if someone failed math twice and science once, the subquery would return:

2
1

You want to know which subject was failed the most (in this case, which subject was failed 2 or more times, since that is the highest number of failures in your subquery). So you count again (also grouping by subject), and use having to return only subjects with 2 or more failures (greater than or equal to the highest value in your subquery).

SELECT SubjectID
FROM Result
WHERE [First/Second_Time] = 1 AND Point < 5
GROUP BY SubjectID
HAVING count(point)...

See https://msdn.microsoft.com/en-us/library/ms178543.aspx for more examples.