many other available ones<\/a> in a typical DBMS. Aggregation queries are queries that use aggregation functions to roll up a table of data into a single (aggregate) row of data, so it\u2019s a way to simplify information or compute new information that depends on 1 or more rows in a table. All semester long we’ve seen that the FROM and WHERE clause build a table that gets passed to the SELECT clause. Aggregate functions appear in the SELECT clause and condense the information in that table into a single row.<\/p>\n\n\n\nFor instance, in the Sailors Reserving Boats database, each sailor has a rating, but together ALL sailors have an average rating. The average rating depends on all the rows in the sailor table, so it\u2019s an aggregate quantity. If we use the Sailors table in the FROM clause but omit the WHERE clause, then the SELECT clause will receive all the rows in the Sailors table. To compute it we write in SQL<\/p>\n\n\n\n
SELECT AVG(rating)\nFROM Sailors<\/pre>\n\n\n\nand see a single number (6.6 as a 1 x 1 table) returned to us with the answer. Note that we passed the name of a single column to the aggregate function AVG. All 5 accept a single column name as their argument. The only one that accepts anything else is COUNT() which also accepts the * wildcard. More on this later. <\/p>\n\n\n\n
We can use aggregation with all the SQL bells and whistles we\u2019re used to: \u201cFind the average rating of sailors older than 20 years old.\u201d In this example, since we’ll need a WHERE clause, the SELECT clause does not receive the entire Sailors table. It only receives certain rows. The SELECT clause computes the average of whatever data it receives, so when it receives only some ratings instead of all ratings, we should expect a different average value from the query.<\/p>\n\n\n\n
SELECT AVG(rating)\nFROM Sailors\nWHERE age < 20<\/pre>\n\n\n\nIndeed in this case the average rating is returned in a 1×1 table as 10.0, which is greater than the 6.6 average of all sailors’ rating. <\/p>\n\n\n\n
One thing to be careful of with aggregation is that you can\u2019t write a query like<\/strong><\/p>\n\n\n\nSELECT sname, AVG(rating)\nFROM Sailors<\/pre>\n\n\n\never because there are many sname values in the sailors table, but only one average rating. You can\u2019t make a table in SQL that has many rows in the first (sname) column and a single row in the next column. It\u2019s not shaped like a table!<\/p>\n\n\n\n
Information Need<\/strong>: \u201cFind the number of sailors.\u201d <\/p>\n\n\n\nSELECT COUNT(*)\nFROM Sailors<\/pre>\n\n\n\nWe just care how many rows are in the table because this table holds records for Sailors. Each row is a sailor. We don\u2019t even need to care about columns. We can thus use * with COUNT instead of the name of any particular column. The interpretation of COUNT(*) is “count rows” without worrying about the values in a particular column. Because we do not have to extract those values and work with them, COUNT(*) can be faster or more efficient, but really it’s just a good practice to use COUNT(*) whenever you know that it’s indeed the rows you’re counting and not particular values. <\/p>\n\n\n\n
Information Need: <\/strong>\u201cFind the number of sailors\u2019 names.\u201d <\/p>\n\n\n\nFor this one, we need to be a little careful, for we have to worry about the duplicate snames like \u2018Horatio\u2019. If we just COUNT(*) we would count Horatio twice, and if we COUNT(sname), we count Horatio twice. We can add the keyword DISTINCT before the column name to remove duplicates:<\/p>\n\n\n\n
SELECT COUNT(DISTINCT sname)\nFROM Sailors<\/pre>\n\n\n\nThis one is straightforward<\/p>\n\n\n\n
Information Need:<\/strong> \u201cFind the highest rating for any sailor.\u201d<\/p>\n\n\n\nSELECT MAX(rating)\nFROM Sailors<\/pre>\n\n\n\nAggregation queries return a single row, guaranteed. If we have a single column in that single row, it\u2019s a 1\u00d71 table, so we can just use rating = and then put a single-column aggregation query in the () to generate a single value!<\/p>\n\n\n\n
Notice I was careful above to say that aggregation returns a single row. We can make multiple-column aggregation queries easily: \u201cFind the average, highest, and lowest ratings for all sailors under 20 years old.\u201d<\/p>\n\n\n\n
SELECT AVG(rating) AS avgRating, MAX(rating) as highRating, MIN(rating) AS lowRating\nFROM Sailors\nWHERE age < 20<\/pre>\n","protected":false},"excerpt":{"rendered":"Computing new values In the SELECT clause we can compute new values from existing data. As a reminder we’ve seen that the SELECT clause determines which column we return in …<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"featured_image_src":null,"featured_image_src_square":null,"_links":{"self":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/860"}],"collection":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/comments?post=860"}],"version-history":[{"count":3,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/860\/revisions"}],"predecessor-version":[{"id":865,"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/pages\/860\/revisions\/865"}],"wp:attachment":[{"href":"https:\/\/enter77.ius.edu\/cjkimmer\/wp-json\/wp\/v2\/media?parent=860"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}