I found this syntax in a online course. I do understand it. But how it works? I do not understand the HAVING COUNT (*) > 1. Does it mean count the rows of the table in my view?
SELECT M1.title, m1.director
FROM MOVIE as M1
INNER JOIN MOVIE as M2 ON M1.director = M2.director
GROUP BY m1.mID, m1.title, m1.director
HAVING COUNT(*) > 1
ORDER BY m1.director, m1.title;
CodePudding user response：
So in your example it is returning rows where the GROUP BY row count is greater than one. Note greater than not equal to.
The GROUP BY with a HAVING clause retrieves the results for the specified group, which match the conditions in the HAVING clause.
The HAVING clause is used with COUNT()instead of a WHERE clause.
Now see the below example, I have used simple data and ordered it to help with the explanation.
You have the below query and are applying to the table TeamMember
SELECT FirstName,COUNT(*)
FROM TeamMember
GROUP BY FirstName
HAVING COUNT(*)>3
TeamMember
------------ ---------------------- --------------------
| StaffCode | FirstName | LastName |
------------ ---------------------- --------------------
| 2343 | Alex | Jones |
| 7323 | Alex | Paker |
| 5676 | Alex | York |
| 6571 | Alex | Long |
| 5325 | Paul | Thomas |
| 5365 | Paul | Wanye |
| 5345 | Paul | Large |
| 3543 | Tom | Lee |
| 2311 | Tom | Freeman |
| 5434 | Zac | Rogers |
------------ ---------------------- --------------------
This is what happens as the causes are added.
Adding the GROUP BY FirstName
---------------------- --------------------
| FirstName | COUNT(*) |
---------------------- --------------------
| Alex | 4 |
| Paul | 3 |
| Tom | 2 |
| Zac | 1 |
---------------------- --------------------
Now adding HAVING COUNT(*)>3
---------------------- --------------------
| FirstName | COUNT(*) |
---------------------- --------------------
| Alex | 4 |
---------------------- --------------------
I have included the below link for further reference if required.