SQL Count function with multiple Where clauses

Question

Can't get a piece of code to work for the life of me.

SELECT FirstName, LastName, sum(CASE WHEN FinalGrade='A' then 1 else 0 end)
FROM Person
JOIN Registration ON Person.Id = Registration.PersonId
JOIN Course ON Person.FacultyId = Course.FacultyId
WHERE Course.FacultyId = 9 AND FinalGrade='A'
GROUP BY FirstName, LastName;

The Tables are:

Person
iD    FirstName    LastName    FacultyId

Registration
iD    CourseId     PersonId    FinalGrade

Course
iD    FacultyId    Name        Code            EAP        GradeType

I want it to return to me

FirstName, LastName, (sum of the number of A's the person got in a specific faculty courses)

It should return a number between 1 and 3 for all, but it returns the time a person got an A * 10

EDIT:

I just did it again from scratch getting it to work by only PersonId, then moving to names and I got it working.

SELECT FirstName, LastName, count(*)
FROM Registration 
JOIN Course ON Course.Id = Registration.CourseId
JOIN Person ON Person.Id = Registration.PersonId
WHERE Course.FacultyId = 9 AND FinalGrade = 'A'
Group by FirstName, LastName;

I still don't quite understand what was wrong, but it's working now that I changed the From and join's around.

WHAT I AND PEOPLE HAVING SIMILAR QUESTIONS CAN TAKE AWAY FROM HERE IS THAT IT MATTERS WHAT TABLE YOU USE WITH FROM CLAUSE.

Your SUM(CASE.. is redundant since you have WHERE .. FinalGrade='A'. If you keep the WHERE..., then change the SUM to COUNT. Also, try grouping by Person.Id instead as it is guaranteed to be unique. — Aiias
– Aiias, Commented May 4, 2014 at 18:00
please add the table names to the fields FirstName, LastName,FinalGrade that makes the structure of the tables clearer — miracle173
– miracle173, Commented May 4, 2014 at 18:13
nevertheless you should use "Group by FirstName, LastName,Person.Id" — miracle173
– miracle173, Commented May 4, 2014 at 18:30

miracle173 · Accepted Answer · 2014-05-04 22:10:07Z

enter image description here Let's simplify the table and use some test data

Person
+----+-----------+----------+-----------+
| Id | FirstName | LastName | FacultyId |
+----+-----------+----------+-----------+
| p1 | John      | Smith    | 9         |
| p2 | Walter    | Jones    | 4         |
+----+-----------+----------+-----------+

Registration
+----+----------+----------+------------+
| Id | CourseId | PersonId | FinalGrade |
+----+----------+----------+------------+
| r1 | c1       | p1       | A          |
| r2 | c2       | p1       | B          |
| r3 | c3       | p2       | A          |
+----+----------+----------+------------+

Course
+----+-----------+--------+
| Id | FacultyId |  Name  |
+----+-----------+--------+
| c1 | 9         | Java   |
| c2 | 9         | Python |
| c3 | 9         | Ruby   |
+----+-----------+--------+

the Join clause (from your first query) gives Person JOIN Registration ON Person.Id = Registration.PersonId

+---------------------------------------+---------------------------------------+
|         Person                        |         Registration                  |
+----+-----------+----------+-----------+----+----------+----------+------------+
| Id | FirstName | LastName | FacultyId | Id | CourseId | PersonId | FinalGrade |
+----+-----------+----------+-----------+----+----------+----------+------------+
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          |
| p1 | John      | Smith    | 9         | r2 | c2       | p1       | B          |
| p2 | Walter    | Jones    | 4         | r3 | c3       | p2       | A          |
+----+-----------+----------+-----------+----+----------+----------+------------+

and joining further with Course gives Person JOIN Registration ON Person.Id = Registration.PersonId JOIN Course ON Person.FacultyId = Course.FacultyId

+---------------------------------------+---------------------------------------+-------------------------+
|         Person                        |         Registration                  |      Course             |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+
| Id | FirstName | LastName | FacultyId | Id | CourseId | PersonId | FinalGrade | Id | FacultyId |  Name  |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c1 | 9         | Java   |
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c2 | 9         | Python |
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c3 | 9         | Ruby   |
| p1 | John      | Smith    | 9         | r2 | c2       | p1       | B          | c1 | 9         | Java   |
| p1 | John      | Smith    | 9         | r2 | c2       | p1       | B          | c2 | 9         | Python |
| p1 | John      | Smith    | 9         | r2 | c2       | p1       | B          | c3 | 9         | Ruby   |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+

now apply the where clause "WHERE Course.FacultyId = 9 AND FinalGrade='A'"

+---------------------------------------+---------------------------------------+-------------------------+
|         Person                        |         Registration                  |      Course             |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+
| Id | FirstName | LastName | FacultyId | Id | CourseId | PersonId | FinalGrade | Id | FacultyId |  Name  |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c1 | 9         | Java   |
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c2 | 9         | Python |
| p1 | John      | Smith    | 9         | r1 | c1       | p1       | A          | c3 | 9         | Ruby   |
+----+-----------+----------+-----------+----+----------+----------+------------+----+-----------+--------+

grouping by "FirstName, LastName" give one group with FirstName='John' and LastName='Smith' and that group contains exactly these 3 records. So the count(*) function on this 'John Smith' group evaluates to 3 and not to 1 as you have expected.

The join condition ON Person.FacultyId = Course.FacultyId (without further restriction) is the problem. It combines each row from the first join with all courses from the same faculty. I also assumed that there it is not necessary that the Person.FacultyId must be the same as the course.FacultyId. But even if they have to be the same the 'Walter Jones' rows would not disapear but the result will remain wrong.

On the other hand you can check that the second query will return exactly what you want for this data.

As I remaked in one of my comments there is another problem with your solution. If there are two students with the same FirstName and LastName you will treat them as one person if you don't distinguish them by ther Person.Id. So you should add Person.Id to the group by clause ( GROUP BY FirstName, LastName, Person.Id) to get a separate record for each student. If you add person.Id to the SELECT clause , too, you will assign the result rows to the appropriate studen even if two students habve the same names.

Naeel Maqsudov · Accepted Answer · 2014-05-04 18:11:20Z

0

Probable you don't need Registration table here or you missed join condition between Registration and Corse tables. If a person linked to several registrations registrations may produce cartesian product with courses.

PS

And pay attention to comments above.

answered May 4, 2014 at 18:11

Naeel Maqsudov

1,45620 silver badges26 bronze badges

Collectives™ on Stack Overflow

SQL Count function with multiple Where clauses

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related