Page 106 - SQL

returns...

House Number_of_Westerosians

Stark 3

Lannister 2

Greyjoy 1

Filter GROUP BY results using a HAVING clause

A HAVING clause filters the results of a GROUP BY expression. Note: The following examples are
using the Library example database.

Examples:

Return all authors that wrote more than one book (live example).

SELECT
a.Id,
a.Name,
COUNT(*) BooksWritten
FROM BooksAuthors ba
INNER JOIN Authors a ON a.id = ba.authorid
GROUP BY
a.Id,
a.Name
HAVING COUNT(*) > 1 -- equals to HAVING BooksWritten > 1
;

Return all books that have more than three authors (live example).

SELECT
b.Id,
b.Title,
COUNT(*) NumberOfAuthors
FROM BooksAuthors ba
INNER JOIN Books b ON b.id = ba.bookid
GROUP BY
b.Id,
b.Title
HAVING COUNT(*) > 3 -- equals to HAVING NumberOfAuthors > 3
;

Basic GROUP BY example

It might be easier if you think of GROUP BY as "for each" for the sake of explanation. The query
below:

SELECT EmpID, SUM (MonthlySalary)
FROM Employee

https://riptutorial.com/ 88