The SQL GROUP BY Statement: Uses and Syntax
The SQL GROUP BY statement is used along with aggregate functions like MIN, MAX, AVG, COUNT, SUM to group the result set or the data returned. For starters SQL GROUP BY is one of the most complicated statements in SQL like the SQL CASE statement. However once the concept of SQL GROUP BY is conceived when it is illustrated with an easy to understand example, the programmer can start playing with it using other clauses and statements in SQL.
The SQL GROUP BY Statement - Syntax:
The syntax for SQL GROUP BY statement is
SELECT columns, aggregate_function(column_name) FROM table_name GROUP BY column_name;
Explantion of the SQL GROUP BY Syntax:
SELECT columns - To select the columns to be displayed or used in the result set.
aggregate_function(column_name) - Aggregate functions like MIN, MAX, AVG, COUNT, SUM are performed over the column
specified in the argument of the aggregate_function
FROM table_name - table_name is the name of the table from which the columns for the aggregate function and the columns to be displayed or used in the result set.
GROUP BY column_name - This part of the syntax is used to group the column (specified by the column_name).
SQL GROUP BY Statement: Real World Examples
To understand the GROUP BY Statement, first let us create a database and a table in it; populate the table (fill the table with
data) and then operate on it.
- Create a database with any database name. In this example, the database is named hub. Write the following code in any SQL
console. I am using a MySQL console.
CREATE DATABASE hub;
- Inorder to create a table in that database, first we have to use the database. To use the database enter the following code.
- Now create a table called ‘scores’ with three fields namely id, name and score. Enter the following code to create the
CREATE TABLE `hub`.`scores` (
`id` INT( 3 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 20 ) NOT NULL ,
`score` INT( 3 ) NOT NULL
) ENGINE = MYISAM CHARACTER SET utf8 COLLATE utf8_general_ci;
- Populate the table using the INSERT INTO statement.
INSERT INTO `hub`.`scores` (`id`, `name`, `score`) VALUES (NULL, ‘Abel’, ‘40’), (NULL, ‘Jack’, ‘70’), (NULL, ‘Turner’,
‘10’), (NULL, ‘Jack’, ‘40’), (NULL, ‘Abel’, ‘80’), (NULL, ‘Turner’, ‘50’);
Now you have a database with a table that is ready to be operated upon using the GROUP BY Statement.
Use the following statements in your MySQL console and see the results:
SELECT name, SUM(score) FROM scores GROUP BY name;
SELECT name, AVG(score) FROM scores GROUP BY name;
SELECT name, MIN(score) FROM scores GROUP BY name;
SELECT name, MAX(score) FROM scores GROUP BY name;
Common Beginner’s Mistakes to Avoid While Using SQL GROUP BY:
Don’t use GROUPBY. GROUP BY are two different words.
This post is part of the series: SQL for Beginners Series
This series of articles on SQL is aimed at those who have started programming in SQL. This series focuses on basic SQL statements like SELECT, DELETE, LIKE, REPLACE, GROUP BY and CASE.