SQL Group By Count & Sum Examples | How to Use Group By in SQL | Study.com

Learn about the SQL “GROUP BY” clause. Review examples of how to use GROUP BY in SQL queries on aggregate functions, such as COUNT, SUM, AVG, MAX, and MIN. Updated: 02/18/2022

Martin has 21 years experience in Information Systems and Information Technology, has a PhD in Information Technology Management, and a master’s degree in Information Systems Management. He is an adjunct professor of computer science and computer programming.

Dan has a B.A. in economics from the University of Pittsburgh and an A.A.S. in computer programming from the Pittsburgh Technical Institute. He has trained others in STEM topics and authored technical documentation throughout his career.

What Is the GROUP BY Clause in SQL?

A laptop displaying SQL code within other languages.

Using normal SQL SELECT statements, each query can only ever return results that are tied to individual records within a database. The GROUP BY clause is used in combination with aggregate functions to calculate totals, averages, minimums, maximums, or other values across multiple records that are related in some way. Aggregate functions are SQL functions that take in a column of values and output a single value.

The GROUP BY clause is very important, as it allows complex analysis to be performed in a variety of ways useful to software developers, data analysts, governmental organizations, and businesses wherever SQL is used.

A SQL SELECT statement using GROUP BY functionality looks like:

SELECT state,
COUNT(*) AS total_vote_count
FROM voter_choice
GROUP BY state;

This GROUP BY statement might be used to count the total number of votes that were cast in an election based upon the state the vote was cast in.

The number of rows that will be produced by a GROUP BY statement will be the total number of unique values in the column being grouped. If the voter_choice table had three unique states, the output from the statement would have three rows for each state along with the count of each row that had that state’s value in its state column:

PA
455

NJ
292

NY
781

This output table shows that the original voter_choice table had 455 total rows with PA in the state column, 781 total rows in the NY column, and 292 total rows in the NJ column.

GROUP BY can also be used to group results based on two or more columns. In this case, the grouping will occur on unique combinations of the two columns. Imagine a table with states and counties that should be grouped. A SELECT statement using GROUP BY for minimum and maximum prices would take this form:

SELECT state,
county,
MIN(price) AS minimum,
MAX(price) AS maximum,
FROM prices
GROUP BY state, county;

This will output the minimum and maximum prices from the table for each unique combination of state and county within the original table.

GROUP BY can also be used with JOIN to group results from the combination of two tables. Consider two tables, one which lists cars by make and model and another which lists purchases that only have the model as a column. A SELECT statement can be made to group total purchases by both make and model.

SELECT car.make,
car.model,
COUNT(*) AS total
FROM car
JOIN purchase
ON car.model = purchase.model
GROUP BY car.make, car.model
ORDER BY car.make ASC;

JOIN combines two tables to create a new output table of results. There are multiple types of JOIN such as INNER JOIN and OUTER JOIN and those which focus on the table on the LEFT or RIGHT. ON stands for the columns to match between the two tables. In the previous example, the car table and the purchase table were both joined based on their model columns, but it is worth noting that column names do not need to match to perform a JOIN.

ORDER BY can be used at the end of a SELECT statement to order the results in ascending (ASC) or descending (DESC) order, meaning increasing or decreasing in order whether a string of text or a numeral value.