(window.webpackJsonp=window.webpackJsonp||[]).push([[3271],{3679:function(t,a,s){"use strict";s.r(a);var e=s(31),r=Object(e.a)({},(function(){var t=this,a=t.$createElement,s=t._self._c||a;return s("ContentSlotsDistributor",{attrs:{"slot-key":t.$parent.slotKey}},[s("h1",{attrs:{id:"group-by"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#group-by"}},[t._v("#")]),t._v(" GROUP BY")]),t._v(" "),s("p",[t._v("Results of a SELECT query can be grouped by one or more columns using the "),s("code",[t._v("GROUP BY")]),t._v(" statement: all results with the same value in the grouped columns are aggregated together. This generates a table of partial results, instead of one result. GROUP BY can be used in conjunction with aggregation functions using the "),s("code",[t._v("HAVING")]),t._v(" statement to define how non-grouped columns are aggregated.")]),t._v(" "),s("h2",{attrs:{id:"basic-group-by-example"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#basic-group-by-example"}},[t._v("#")]),t._v(" Basic GROUP BY example")]),t._v(" "),s("p",[t._v('It might be easier if you think of GROUP BY as "for each" for the sake of explanation. The query below:')]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" EmpID"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("SUM")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),t._v("MonthlySalary"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" \n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Employee\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" EmpID\n\n")])])]),s("p",[t._v("is saying:")]),t._v(" "),s("p",[t._v("\"Give me the sum of MonthlySalary's "),s("strong",[t._v("for each")]),t._v(' EmpID"')]),t._v(" "),s("p",[t._v("So if your table looked like this:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("EmpID"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("MonthlySalary"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("200")]),t._v("          "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("300")]),t._v("          "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n\n")])])]),s("p",[t._v("Result:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("200")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("300")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n\n")])])]),s("p",[t._v("Sum wouldn't appear to do anything because the sum of one number is that number.  On the other hand if it looked like this:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("EmpID"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("MonthlySalary"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("200")]),t._v("          "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("300")]),t._v("          "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("300")]),t._v("          "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-----+-------------+")]),t._v("\n\n")])])]),s("p",[t._v("Result:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("500")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("2")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("300")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("|")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("-")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("+")]),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("---+")]),t._v("\n\n")])])]),s("p",[t._v("Then it would because there are two EmpID 1's to sum together.")]),t._v(" "),s("h2",{attrs:{id:"use-group-by-to-count-the-number-of-rows-for-each-unique-entry-in-a-given-column"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#use-group-by-to-count-the-number-of-rows-for-each-unique-entry-in-a-given-column"}},[t._v("#")]),t._v(" USE GROUP BY to COUNT the number of rows for each unique entry in a given column")]),t._v(" "),s("p",[t._v("Let's say you want to generate counts or subtotals for a given value in a column.")]),t._v(" "),s("p",[t._v('Given this table, "Westerosians":')]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("Name")]),t._v(" "),s("th",[t._v("GreatHouseAllegience")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Arya")]),t._v(" "),s("td",[t._v("Stark")])]),t._v(" "),s("tr",[s("td",[t._v("Cercei")]),t._v(" "),s("td",[t._v("Lannister")])]),t._v(" "),s("tr",[s("td",[t._v("Myrcella")]),t._v(" "),s("td",[t._v("Lannister")])]),t._v(" "),s("tr",[s("td",[t._v("Yara")]),t._v(" "),s("td",[t._v("Greyjoy")])]),t._v(" "),s("tr",[s("td",[t._v("Catelyn")]),t._v(" "),s("td",[t._v("Stark")])]),t._v(" "),s("tr",[s("td",[t._v("Sansa")]),t._v(" "),s("td",[t._v("Stark")])])])]),t._v(" "),s("p",[t._v("Without GROUP BY, COUNT will simply return a total number of rows:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("Count")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" Number_of_Westerosians\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Westerosians\n\n")])])]),s("p",[t._v("returns...")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("Number_of_Westerosians")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("6")])])])]),t._v(" "),s("p",[t._v("But by adding GROUP BY, we can COUNT the users for each value in a given column, to return the number of people in a given Great House, say:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" GreatHouseAllegience House"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("Count")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" Number_of_Westerosians\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Westerosians\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" GreatHouseAllegience\n\n")])])]),s("p",[t._v("returns...")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("House")]),t._v(" "),s("th",[t._v("Number_of_Westerosians")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Stark")]),t._v(" "),s("td",[t._v("3")])]),t._v(" "),s("tr",[s("td",[t._v("Greyjoy")]),t._v(" "),s("td",[t._v("1")])]),t._v(" "),s("tr",[s("td",[t._v("Lannister")]),t._v(" "),s("td",[t._v("2")])])])]),t._v(" "),s("p",[t._v("It's common to combine GROUP BY with ORDER BY to sort results by largest or smallest category:")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v(" GreatHouseAllegience House"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("Count")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" Number_of_Westerosians\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" Westerosians\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" GreatHouseAllegience\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ORDER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v(" Number_of_Westerosians "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Desc")]),t._v("\n\n")])])]),s("p",[t._v("returns...")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("House")]),t._v(" "),s("th",[t._v("Number_of_Westerosians")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Stark")]),t._v(" "),s("td",[t._v("3")])]),t._v(" "),s("tr",[s("td",[t._v("Lannister")]),t._v(" "),s("td",[t._v("2")])]),t._v(" "),s("tr",[s("td",[t._v("Greyjoy")]),t._v(" "),s("td",[t._v("1")])])])]),t._v(" "),s("h2",{attrs:{id:"filter-group-by-results-using-a-having-clause"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#filter-group-by-results-using-a-having-clause"}},[t._v("#")]),t._v(" Filter GROUP BY results using a HAVING clause")]),t._v(" "),s("p",[t._v("A HAVING clause filters the results of a GROUP BY expression. Note: The following examples are using the "),s("a",{attrs:{href:"http://stackoverflow.com/documentation/sql/280/example-databases/4978/library-database#t=201607232118488473251",target:"_blank",rel:"noopener noreferrer"}},[t._v("Library"),s("OutboundLink")],1),t._v(" example database.")]),t._v(" "),s("p",[s("strong",[t._v("Examples:")])]),t._v(" "),s("p",[t._v("Return all authors that wrote more than one book ("),s("a",{attrs:{href:"http://sqlfiddle.com/#!9/7c06f/7",target:"_blank",rel:"noopener noreferrer"}},[t._v("live example"),s("OutboundLink")],1),t._v(").")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("\n  a"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  a"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("COUNT")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" BooksWritten\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" BooksAuthors ba\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INNER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" Authors a "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" a"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("id "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" ba"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("authorid\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v("\n  a"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  a"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Name\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("HAVING")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("COUNT")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("1")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-- equals to HAVING BooksWritten > 1")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("p",[t._v("Return all books that have more than three authors ("),s("a",{attrs:{href:"http://sqlfiddle.com/#!9/7c06f/9",target:"_blank",rel:"noopener noreferrer"}},[t._v("live example"),s("OutboundLink")],1),t._v(").")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("SELECT")]),t._v("\n  b"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  b"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Title"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("COUNT")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" NumberOfAuthors\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("FROM")]),t._v(" BooksAuthors ba\n  "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("INNER")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("JOIN")]),t._v(" Books b "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("ON")]),t._v(" b"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("id "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("=")]),t._v(" ba"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("bookid\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("GROUP")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("BY")]),t._v("\n  b"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Id"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("\n  b"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(".")]),t._v("Title\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("HAVING")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token function"}},[t._v("COUNT")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v("(")]),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v("*")]),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(")")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token operator"}},[t._v(">")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token number"}},[t._v("3")]),t._v("    "),s("span",{pre:!0,attrs:{class:"token comment"}},[t._v("-- equals to HAVING NumberOfAuthors > 3")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(";")]),t._v("\n\n")])])]),s("h2",{attrs:{id:"rolap-aggregation-data-mining"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#rolap-aggregation-data-mining"}},[t._v("#")]),t._v(" ROLAP aggregation (Data Mining)")]),t._v(" "),s("h3",{attrs:{id:"description"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#description"}},[t._v("#")]),t._v(" "),s("strong",[t._v("Description")])]),t._v(" "),s("p",[t._v('The SQL standard provides two additional aggregate operators. These use the polymorphic value "ALL" to denote the set of all values ​​that an attribute can take.\nThe two operators are:')]),t._v(" "),s("ul",[s("li",[s("code",[t._v("with data cube")]),t._v(" that it provides all possible combinations than the argument attributes of the clause.")]),t._v(" "),s("li",[s("code",[t._v("with roll up")]),t._v(" that it provides the aggregates obtained by considering the attributes in order from left to right compared how they are listed in the argument of the clause.")])]),t._v(" "),s("p",[t._v("SQL standard versions that support these features: 1999,2003,2006,2008,2011.")]),t._v(" "),s("h3",{attrs:{id:"examples"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#examples"}},[t._v("#")]),t._v(" "),s("strong",[t._v("Examples")])]),t._v(" "),s("p",[t._v("Consider this table:")]),t._v(" "),s("table",[s("thead",[s("tr",[s("th",[t._v("Food")]),t._v(" "),s("th",[t._v("Brand")]),t._v(" "),s("th",[t._v("Total_amount")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand1")]),t._v(" "),s("td",[t._v("100")])]),t._v(" "),s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("250")])]),t._v(" "),s("tr",[s("td",[t._v("Pizza")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("300")])])])]),t._v(" "),s("h3",{attrs:{id:"with-cube"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#with-cube"}},[t._v("#")]),t._v(" With cube")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("select")]),t._v(" Food"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Brand"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Total_amount\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("from")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Table")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("group")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("by")]),t._v(" Food"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Brand"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Total_amount "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("with")]),t._v(" cube\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("Food")]),t._v(" "),s("th",[t._v("Brand")]),t._v(" "),s("th",[t._v("Total_amount")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand1")]),t._v(" "),s("td",[t._v("100")])]),t._v(" "),s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("250")])]),t._v(" "),s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("350")])]),t._v(" "),s("tr",[s("td",[t._v("Pizza")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("300")])]),t._v(" "),s("tr",[s("td",[t._v("Pizza")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("300")])]),t._v(" "),s("tr",[s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("Brand1")]),t._v(" "),s("td",[t._v("100")])]),t._v(" "),s("tr",[s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("550")])]),t._v(" "),s("tr",[s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("650")])])])]),t._v(" "),s("h3",{attrs:{id:"with-roll-up"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#with-roll-up"}},[t._v("#")]),t._v(" With roll up")]),t._v(" "),s("div",{staticClass:"language-sql extra-class"},[s("pre",{pre:!0,attrs:{class:"language-sql"}},[s("code",[s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("select")]),t._v(" Food"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Brand"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Total_amount\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("from")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("Table")]),t._v("\n"),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("group")]),t._v(" "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("by")]),t._v(" Food"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Brand"),s("span",{pre:!0,attrs:{class:"token punctuation"}},[t._v(",")]),t._v("Total_amount "),s("span",{pre:!0,attrs:{class:"token keyword"}},[t._v("with")]),t._v(" roll up\n\n")])])]),s("table",[s("thead",[s("tr",[s("th",[t._v("Food")]),t._v(" "),s("th",[t._v("Brand")]),t._v(" "),s("th",[t._v("Total_amount")])])]),t._v(" "),s("tbody",[s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand1")]),t._v(" "),s("td",[t._v("100")])]),t._v(" "),s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("250")])]),t._v(" "),s("tr",[s("td",[t._v("Pizza")]),t._v(" "),s("td",[t._v("Brand2")]),t._v(" "),s("td",[t._v("300")])]),t._v(" "),s("tr",[s("td",[t._v("Pasta")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("350")])]),t._v(" "),s("tr",[s("td",[t._v("Pizza")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("300")])]),t._v(" "),s("tr",[s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("ALL")]),t._v(" "),s("td",[t._v("650")])])])]),t._v(" "),s("h4",{attrs:{id:"syntax"}},[s("a",{staticClass:"header-anchor",attrs:{href:"#syntax"}},[t._v("#")]),t._v(" Syntax")]),t._v(" "),s("li",[s("p",[t._v("GROUP BY {"),s("br"),t._v("\n       column-expression"),s("br"),t._v("\n       | ROLLUP ( "),s("group_by_expression",[t._v(" [ ,...n ] )"),s("br"),t._v("\n       | CUBE ( "),s("group_by_expression",[t._v(" [ ,...n ] )"),s("br"),t._v("\n       | GROUPING SETS ( [ ,...n ] )"),s("br"),t._v("\n       | () --calculates the grand total"),s("br"),t._v("\n} [ ,...n ]")])],1)],1)]),t._v(" "),s("li",[s("p",[s("group_by_expression",[t._v(" ::="),s("br"),t._v("\n      column-expression"),s("br"),t._v("\n    | ( column-expression [ ,...n ] )")])],1)]),t._v(" "),s("li",[s("p",[s("grouping_set",[t._v(" ::="),s("br"),t._v("\n      () --calculates the grand total"),s("br"),t._v("\n    | "),s("grouping_set_item",[s("br"),t._v("\n    | ( "),s("grouping_set_item",[t._v(" [ ,...n ] )")])],1)],1)],1)]),t._v(" "),s("li",[s("p",[s("grouping_set_item",[t._v(" ::="),s("br"),t._v(" "),s("group_by_expression",[s("br"),t._v("\n    | ROLLUP ( "),s("group_by_expression",[t._v(" [ ,...n ] )"),s("br"),t._v("\n    | CUBE ( "),s("group_by_expression",[t._v(" [ ,...n ] )")])],1)],1)],1)],1)])])}),[],!1,null,null,null);a.default=r.exports}}]);