how to get total count of partial counts

Question

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

Answer 1

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.