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).
Anuj holds professional certifications in Google Cloud, AWS as well as certifications in Docker and App Performance Tools such as New Relic. He specializes in Cloud Security, Data Encryption and Container Technologies.
Anuj Varma – who has written 1209 posts on Anuj Varma, Hands-On Technology Architect, Clean Air Activist.
Leave a Reply