Online College Courses for Credit

+
1 Tutorials that teach GROUP BY to Combine Data
Take your pick:
GROUP BY to Combine Data

GROUP BY to Combine Data

Rating:
(0)
Author: Sophia Tutorial
Description:

Identify a query that correctly uses GROUP BY to show aggregate data from a set of columns.

(more)
See More
Fast, Free College Credit

Developing Effective Teams

Let's Ride
*No strings attached. This college course is 100% free and is worth 1 semester credit.

47 Sophia partners guarantee credit transfer.

299 Institutions have accepted or given pre-approval for credit transfer.

* The American Council on Education's College Credit Recommendation Service (ACE Credit®) has evaluated and recommended college credit for 33 of Sophia’s online courses. Many different colleges and universities consider ACE CREDIT recommendations in determining the applicability to their course and degree programs.

Tutorial

what's covered
This tutorial explores the GROUP BY clause in a SELECT statement to divide rows into groups.

The GROUP BY clause in the SELECT statement can help to divide the rows that are being returned from the SELECT statement into specific groups. For each of those items in the group, we can then apply an aggregate function among that group. What we have worked with so far with aggregate functions returns a single aggregate value across the result set. Using the GROUP BY clause, we can have separations of groups with the aggregates calculated within that group of rows.

The structure of a statement that uses the GROUP BY clause would look like this:

SELECT <column1>…., <aggregate function>
FROM <tablename>
GROUP BY <column1>…;

We have the columns that we want to group in both the SELECT clause and the GROUP BY clause. For example, if we wanted to get the number of customers that we have in each country, we can use the GROUP BY clause like:

SELECT country, COUNT(*)
FROM customer
GROUP BY country;

File:11316-1365-1.png

It is important that the column is listed in both the SELECT and GROUP BY clause. If we do not include it in the GROUP BY clause:

SELECT country, COUNT(*)
FROM customer;

We will get an error:

File:11317-1365-2.png

If we did not have the column in the SELECT clause:

SELECT COUNT(*)
FROM customer
GROUP BY country;

There will not be an error that is generated by the result set isn’t useful at all as it doesn’t indicate what the aggregate function is in relation to:

File:11318-1365-3.png

As you see in the result set above, even though we grouped based on country, we do not know which country each of the counts is for.

We can run something similar to find the total amount of orders by country from the invoice table:

SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country;

File:11319-1365-4.png

We can also add more complexity by grouping by multiple columns and returning more than one aggregate value. For example, if we wanted to find the total and average of each invoice based on the state and country, we can do the following:

SELECT billing_country, billing_state, SUM(total), ROUND(AVG(total),2)
FROM invoice
GROUP BY billing_country, billing_state
ORDER BY billing_country, billing_state;

Notice above, we have added the ORDER BY clause so that they are sorted in an order that logical sense:

File:11320-1365-5.png

The possibilities are endless for us to group the data and run aggregate functions on those subsets of data.

try it
Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.

summary
The GROUP BY clause allows us to divide rows into groups and then apply aggregate functions to each of the individual groups.