Show the subjects per StudentNo and the count of number of subjects per student

Error: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

SELECT Subject, StudentNo, SUM(COUNT(DISTINCT Subject)) AS NumOfSubjectPerStudent   
FROM Subjects AS S
INNER JOIN STUDENTS AS ST ON S.ID = ST.ID
WHERE S.ID = ST.ID
GROUP BY ST.StudentNo, S.Subject
ORDER BY ST.StudentNo DESC

Solution

I think you're almost there but without knowing the structure of the Students and Subjects tables, I can only assume it should be something like this:

SELECT ST.StudentNo, S.Subject, SUM(COUNT(DISTINCT S.Subject)) AS NumOfSubjectPerStudent   
FROM Subjects AS S
    INNER JOIN STUDENTS AS ST ON S.StudentId = ST.ID
GROUP BY ST.StudentNo, S.Subject
ORDER BY ST.StudentNo DESC

This assumption is based on the Subjects table having a StudentId field that links to the Students Id field.

I also am assuming that the Subjets Id field is the unique identifier/primary key for that Subject and shouldn't be used to JOIN against the Subjects ID field.

If I am wrong with my assumptions, then can you please clarify the columns in each table, and also provide an example of data in each table to better make sense of how to help you.