SQL GROUP BY Statement and It's Really Intersting Applications

SQL GROUP BY Statement and It's Really Intersting Applications
Page content

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.

  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;

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

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

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

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.

  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