Group by (SQL)

<h2 id="examples">Examples</h2>
<p>Returns a list of Department IDs along with the sum of their sales for the date of January 1, 2000.  
</p>
   SELECT DeptID, SUM(SaleAmount) FROM Sales
    WHERE SaleDate = '01-Jan-2000'
 GROUP BY DeptID

<p>In the following example one can ask "How many <i>units</i> were sold in each <i>region</i> for every <i>ship date?</i>":
</p>
<table><tbody><tr><th>Sum of units</th><th>Ship date ▼</th></tr><tr><th>Region ▼</th><th>2005-01-31</th><th>2005-02-28</th><th>2005-03-31</th><th>2005-04-30</th><th>2005-05-31</th><th>2005-06-30</th></tr><tr><td>East</td><td>66</td><td>80</td><td>102</td><td>116</td><td>127</td><td>125</td></tr><tr><td>North</td><td>96</td><td>117</td><td>138</td><td>151</td><td>154</td><td>156</td></tr><tr><td>South</td><td>123</td><td>141</td><td>157</td><td>178</td><td>191</td><td>202</td></tr><tr><td>West</td><td>78</td><td>97</td><td>117</td><td>136</td><td>150</td><td>157</td></tr><tr><td>(blank)</td><td></td><td></td><td></td><td></td><td></td><td></td></tr><tr><td>Grand total</td><td>363</td><td>435</td><td>514</td><td>581</td><td>622</td><td>640</td></tr></tbody></table>
<p>The following code returns the data of the above <a href="/facts/Pivot_table/FwXKTJog">pivot table</a> which answers the question "How many units were sold in each region for every ship date?":
</p>
   SELECT Region, Ship_Date, SUM(Units) AS Sum_of_Units
     FROM FlatData
 GROUP BY Region, Ship_Date

<h2 id="common-groupings">Common groupings</h2>
<p>Common grouping (<a href="/facts/Aggregate_function/685obrqz">aggregation</a>) functions include:
</p>
<ul><li>Count(<i>expression</i>) - Quantity of matching records (per group)</li>
<li>Sum(<i>expression</i>) - Summation of given value (per group)</li>
<li>Min(<i>expression</i>) - Minimum of given value (per group)</li>
<li>Max(<i>expression</i>) - Maximum of given value (per group)</li>
<li>Avg(<i>expression</i>) - Average of given value (per group)</li></ul>
<h2 id="see-also">See also</h2>
<ul><li><a href="/facts/Aggregate_function/685obrqz">Aggregate function</a></li></ul>

<h2 id="external-links">External links</h2>
<ul><li><a href="http://www.sqlsnippets.com/en/topic-13100.html">SQL Snippets: SQL Features Tutorials - Grouping Rows with GROUP BY</a></li></ul>

<h2 id="references">References</h2>

<ol>
<li id="fn:1"><p>"SQL GROUP BY Statement". www.w3schools.com. Retrieved 2020-09-18. <a href="https://www.w3schools.com/sql/sql_groupby.asp" target="_blank">https://www.w3schools.com/sql/sql_groupby.asp</a> <a href="#fnref:1" class="footnote-back-ref">↩</a></p></li>
<li id="fn:2"><p>shkale-msft. "GROUP BY (Transact-SQL) - SQL Server". docs.microsoft.com. Retrieved 2020-09-18. <a href="https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql" target="_blank">https://docs.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql</a> <a href="#fnref:2" class="footnote-back-ref">↩</a></p></li>
<li id="fn:3"><p>"SQL Grouping and Aggregation". databaselecture.com. Retrieved 2020-12-09. <a href="http://www.databaselecture.com/sql.html" target="_blank">http://www.databaselecture.com/sql.html</a> <a href="#fnref:3" class="footnote-back-ref">↩</a></p></li>
</ol>

Group by (SQL) open-in-new

Group by (SQL)