Pin Me

SQL Group By Statement: Explanation & Examples

written by: vishalseafarer•edited by: Amber Neely•updated: 10/10/2010

Learn how to use the SQL GROUP BY statement that is illustrated with an easy to understand example. The SQL GROUP BY statement can be used to create very interesting applications once it's ease of use is understood.

  • slide 1 of 2

    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).

  • slide 2 of 2

    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.

    1) 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;

    2) Inorder to create a table in that database, first we have to use the database. To use the database enter the following code.

    USE hub;

    3) Now create a table called 'scores' with three fields namely id, name and score. Enter the following code to create the

    table.

    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;

    4) 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;

    SQL GROUP BY 

    SQL GROUP BY Common Beginner's Mistakes to Avoid While Using SQL GROUP BY:

    Don't use GROUPBY. GROUP BY are two different words.

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.
  1. SQL Group By Statement: Explanation & Examples
  2. SQL CASE Expressions: Explanation & Examples
  3. SQL Select Statement: Syntax & Examples
  4. SQL Delete Statement: Syntax & Examples