Copyright

SQL: GROUP BY Clause

Instructor: Marcia Wert

Marcia has taught Information technology and Mathematics with a master's degree in IT

When we go online shopping, we might have a favorite color for clothing, and perhaps we'd like to arrange them by color. The group by statement performs this function in SQL. In this lesson, we'll learn what the statement is and how it is used.

What is SQL?

SQL or Structured Query Language is employed only in relational databases. SQL is a special language that allows us to communicate with those database. As you know, relational databases are electronic tables that house enormous amounts of data. The GROUP BY clause is a special query that allow us to ask question but also group or order the data that is retrieved. The GROUP By clause also allows us to organize our resulting data in terms of specific groups. This language can be paired with programming code such as C++, Java, or Visual C to retrieve data from databases.

It is formatted like this:

SELECT column1, column2
FROM table_name
WHERE [conditions]
GROUP BY column1, column2
ORDER BY column1, column2

It is paired with the SELECT statement and the GROUP BY always proceeds the ORDER BY. It is not necessary to include the ORDER BY if you do not want your information that you selected to be ordered.

Let's take that scenario and see what would be produced from a table called Merchandise.

Grouping Without an Order

The following table represents sweaters of two different colors, each item having a unique Merchandise ID called Merchandise_id.

Merchandise_id Color Manufacturer_id
459965 Red 678
344789 Blue 725
478346 Red 645
476904 Red 678
476800 Blue 678

Now, we wish to select only the Merchandise_id from our table where the color is red without the ORDER BY statement.

We would use this SELECT statement to accomplish it.

SELECT Merchandise_id
FROM Merchandise
WHERE Manufacturer_id = 678
GROUP BY Color;

The resulting query would produce:

Merchandise_id Color Manufacturer_id
476800 Blue 678
459965 Red 678
476904 Red 678

Since we only grouped by color our Merchandise_id is out of order and we have a blue sweater along with our red sweaters as a result.

Now this time, we'll order by the Merchandise_id.

Grouping With an Order

Here's the original table again:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support