{"id":429,"date":"2021-11-11T11:43:21","date_gmt":"2021-11-11T11:43:21","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/?page_id=429"},"modified":"2021-11-17T13:07:09","modified_gmt":"2021-11-17T13:07:09","slug":"aggregate-functions","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/higher\/aggregate-functions\/","title":{"rendered":"Aggregate Functions and GROUP BY"},"content":{"rendered":"

Examples are based on the GoGo Gadgets<\/a> database<\/strong><\/p>\n

Aggregate Functions are calculations applied to all the records found from a SELECT command.<\/p>\n

AVG(xxx) -- return the average value of specified field or expression\r\nMAX(xxx) -- return largest value for specified field or expression\r\nMIN(xxx) -- return smallest value for specified field or expression\r\nSUM(xxx) -- return the total for specified field or expression\r\nCOUNT()  -- return number of records found<\/pre>\n
Example:<\/p>\n
SELECT MIN(price), MAX(price)\r\nFROM item<\/pre>\n\n\n\n\n
<\/td>\nMIN(price)<\/strong><\/td>\nMAX(price)<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n2.0<\/td>\n39.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Aliases<\/h2>\n
Aliases can be used to give more readable headings for columns.<\/p>\n
SELECT MIN(price) AS "Cheapest Price", \r\n    MAX(price) AS "Dearest Price"\r\nFROM item;<\/pre>\n\n\n\n\n
<\/td>\nCheapest Price<\/strong><\/td>\nDearest Price<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n2.0<\/td>\n39.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Aggregate and Non-Aggregate Expressions<\/h2>\n
Mixing non-aggregate and aggregate expressions in a SELECT statement is not permitted without Grouping<\/p>\n
The query below is illegal, as it mixes non-aggregate productName with the aggregate function MIN – it does not return the name of the products with the minumum price, it returns the name of the first product that matches the minimum price.<\/p>\n
SELECT itemID, description, MIN(price)\r\nFROM item<\/pre>\n\n\n\n\n
<\/td>\nItemID<\/strong><\/td>\nDescription<\/strong><\/td>\nMIN(price)<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n868<\/td>\nScreen Wipes<\/td>\n2.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Calculations and Other Functions<\/h2>\n
Aggregating functions can use other calculations:<\/p>\n
SELECT MAX(price*quantity) \r\nFROM OrderItem, Item \r\nWHERE OrderItem.ItemID = Item.ItemID<\/pre>\n\n\n\n\n
<\/td>\nMAX(price*quantity)<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n88.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Other functions can be used to modify the results of aggregate functions:<\/p>\n
SELECT ROUND(AVG(price),2) AS "Average Item Price"\r\nFROM item;<\/pre>\n\n\n\n\n
<\/td>\nAverage Item Price<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n13.7<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
GROUP BY<\/h2>\n
Aggregate functions return single rows of values, as in the examples above.<\/p>\n
Groups are used with aggregate functions to get results for each group. The SELECT clause should include a non-aggregate expression (the name of the group) to label each group:<\/p>\n
Example: Find the average price for each item category:<\/p>\n
SELECT category, ROUND(AVG(price),2) AS "Average Price"\r\nFROM item\r\nGROUP BY category;<\/pre>\n\n\n\n\n\n\n\n\n
<\/td>\nCategory<\/strong><\/td>\nAverage Price<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\nBoys Toys<\/td>\n14.1<\/td>\n<\/tr>\n
2<\/strong><\/td>\nComputer Accessories<\/td>\n13.0<\/td>\n<\/tr>\n
3<\/strong><\/td>\nGirls Gizmos<\/td>\n11.29<\/td>\n<\/tr>\n
4<\/strong><\/td>\nOffice Distractions<\/td>\n10.89<\/td>\n<\/tr>\n
5<\/strong><\/td>\nPersonal Grooming<\/td>\n18.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
The GROUP BY clause must be after any WHERE clause, and before any ORDER BY clause:<\/p>\n
SELECT category, ROUND(AVG(price),2) AS "Average Price"\r\nFROM item\r\nWHERE category NOT LIKE "%toys%"\r\nGROUP BY category\r\nORDER BY ROUND(AVG(price),2) DESC<\/pre>\n\n\n\n\n\n\n\n
<\/td>\nCategory<\/strong><\/td>\nAverage Price<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\nPersonal Grooming<\/td>\n18.0<\/td>\n<\/tr>\n
2<\/strong><\/td>\nComputer Accessories<\/td>\n13.0<\/td>\n<\/tr>\n
3<\/strong><\/td>\nGirls Gizmos<\/td>\n11.29<\/td>\n<\/tr>\n
4<\/strong><\/td>\nOffice Distractions<\/td>\n10.89<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
Grouping can be applied to single fields, or multiple fields.<\/p>\n
Example: find total sales for each city\/region and category for orders that have not been dispatched:<\/p>\n
SELECT Address3 as "City\/Region", Category, SUM(price*quantity) AS "Sales Pending"\r\nFROM Customer c, CustOrder co, OrderItem oi, Item i\r\nWHERE c.CustomerID = co.CustomerID\r\nAND co.OrderNo = oi.OrderNo\r\nAND oi.ItemID = i.ItemID\r\nAND orderDispatched = ""\r\nGROUP BY Address3, Category<\/pre>\n\n\n\n\n\n\n\n\n\n\n\n\n
<\/td>\nCity\/Region<\/strong><\/td>\nCategory<\/strong><\/td>\nSales Pending<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\nAberdeen<\/td>\nBoys Toys<\/td>\n4.0<\/td>\n<\/tr>\n
2<\/strong><\/td>\nAberdeen<\/td>\nComputer Accessories<\/td>\n78.0<\/td>\n<\/tr>\n
3<\/strong><\/td>\nAberdeen<\/td>\nGirls Gizmos<\/td>\n12.0<\/td>\n<\/tr>\n
4<\/strong><\/td>\nAberdeen<\/td>\nPersonal Grooming<\/td>\n30.0<\/td>\n<\/tr>\n
5<\/strong><\/td>\nEdinburgh<\/td>\nBoys Toys<\/td>\n14.0<\/td>\n<\/tr>\n
6<\/strong><\/td>\nEdinburgh<\/td>\nComputer Accessories<\/td>\n14.0<\/td>\n<\/tr>\n
7<\/strong><\/td>\nGlasgow<\/td>\nComputer Accessories<\/td>\n22.0<\/td>\n<\/tr>\n
8<\/strong><\/td>\nGlasgow<\/td>\nGirls Gizmos<\/td>\n32.0<\/td>\n<\/tr>\n
9<\/strong><\/td>\nGlasgow<\/td>\nPersonal Grooming<\/td>\n34.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
Examples are based on the GoGo Gadgets database Aggregate Functions are calculations applied to all the records found from a SELECT command. AVG(xxx) — return the average value of specified field or expression MAX(xxx) — return largest value for specified field or expression MIN(xxx) — return smallest value for specified field or expression SUM(xxx) — return the total for specified field or expression COUNT() — return number of records found<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"parent":417,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-429","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/429","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/comments?post=429"}],"version-history":[{"count":23,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/429\/revisions"}],"predecessor-version":[{"id":602,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/429\/revisions\/602"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/417"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/media?parent=429"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}