sql aggregates and group by
When you select an aggregate (such as count (*), ) you also need to do a group by on the results.
Select a.Name,count(b.title) as NumberOfBooks from AUTHOR a join BOOKS b on a.ID = b.AuthorID group by a.Name (this finds the NUMBER OF BOOKS written by each AUTHOR)
Side Note – Find the MAX occurrences of a value from a list of possible values
To do an equivalent of a MAX function (from a list of values, find the value with the maximum occurrences), just do a group by as illustrated above, followed by an order by DESC
Select a.Name,count(b.title) as NumberOfBooks from AUTHOR a join BOOKS b on a.ID = b.AuthorID group by a.Name order by NumberOfBooks DESC (This ORDERS The NUMBER Of BOOKS in DESCENDING ORDER, so the MAX is on top).
Leave a Reply