{"id":572,"date":"2021-11-16T16:26:48","date_gmt":"2021-11-16T16:26:48","guid":{"rendered":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/?page_id=572"},"modified":"2021-11-17T13:08:35","modified_gmt":"2021-11-17T13:08:35","slug":"having-clause","status":"publish","type":"page","link":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/advanced-higher\/having-clause\/","title":{"rendered":"HAVING Clause"},"content":{"rendered":"

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

Aggregate functions (sum, count, max, min, avg) are used to create summarised GROUP BY results.<\/p>\n

However these summarised results cannot be restricted in a WHERE clause – which is used to specify which individual records<\/strong> are to be included in the results.<\/p>\n

The HAVING clause is used to filter the results from aggregate functions summarised GROUP BY.<\/p>\n

-- display total sales for each item\r\nSELECT Item.itemID, SUM(quantity*price) AS "Total Sales"\r\nFROM OrderItem, Item\r\nWHERE OrderItem.ItemID = Item.ItemID\r\nGROUP BY Item.itemID\r\nORDER BY SUM(quantity*price) DESC\r\n<\/pre>\n\n\n\n\n\n\n\n
<\/td>\nItemID<\/strong><\/td>\nTotal Sales<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n7072<\/td>\n378.0<\/td>\n<\/tr>\n
2<\/strong><\/td>\n9605<\/td>\n208.0<\/td>\n<\/tr>\n
…<\/strong><\/td>\n…<\/td>\n…<\/td>\n<\/tr>\n
42<\/strong><\/td>\n5986<\/td>\n4.0<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
-- display items where total sales greater than 200\r\nSELECT Item.itemID, SUM(quantity*price) AS "Total Sales"\r\nFROM OrderItem, Item\r\nWHERE OrderItem.ItemID = Item.ItemID\r\nGROUP BY Item.itemID\r\nHAVING SUM(quantity*price) > 200\r\nORDER BY SUM(quantity*price) DESC\r\n<\/pre>\n\n\n\n\n\n\n\n
<\/td>\nItemID<\/strong><\/td>\nTotal Sales<\/strong><\/td>\n<\/tr>\n
1<\/strong><\/td>\n7072<\/td>\n378.0<\/td>\n<\/tr>\n
2<\/strong><\/td>\n9605<\/td>\n208.0<\/td>\n<\/tr>\n
3<\/strong><\/td>\n7108<\/td>\n204.0<\/td>\n<\/tr>\n
4<\/strong><\/td>\n6584<\/td>\n204.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 (sum, count, max, min, avg) are used to create summarised GROUP BY results. However these summarised results cannot be restricted in a WHERE clause – which is used to specify which individual records are to be included in the results. The HAVING clause is used to filter the results from aggregate functions summarised GROUP BY. — display total sales for<\/p>\n
Read More<\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"parent":533,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-572","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/572","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=572"}],"version-history":[{"count":9,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/572\/revisions"}],"predecessor-version":[{"id":603,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/572\/revisions\/603"}],"up":[{"embeddable":true,"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/pages\/533"}],"wp:attachment":[{"href":"https:\/\/blogs.glowscotland.org.uk\/sh\/ahscomputingsql\/wp-json\/wp\/v2\/media?parent=572"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}