0

An extension to this Question here, how can I fetch the count for ALL the students who have reached their maximum allowed absents.

I tried this

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance)
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   configurations)
) abc

but it is not working and gives this error:

No column was specified for column 2 of 'abc'.

Environment: SQL Server 2000

5

Try adding a column name for COUNT(attendance) - such as CountAttendance.

SELECT COUNT(*) FROM
(
SELECT student_id,
       COUNT(attendance) as CountAttendance
FROM   student_attendance
WHERE  attendance = 'ABSENT'
GROUP  BY student_id
HAVING COUNT(attendance) = (SELECT max_allowed
                            FROM   [configuration])
) a ;

Removing COUNT(attendance) from the SELECT clause of the subquery should also work (as you found out).

But why a non-aliased COUNT column causes issue?

Because all columns in a subquery should be named/aliased. Some DBMS (and some versions) don't require this, as they provide arbitrary names themselves. Others, like SQL Server, are more restrictive.

You could even have a single column with SELECT 1 AS c in the subquery or SELECT NULL AS c. The outer query needs only to count rows with COUNT(*) so the column list in the subquery is completely irrelevant. What matter are the WHERE and HAVING clauses.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.