Skip to content

SQL GROUP BY

SQL GROUP BY clause with query example – Group By statement is used to group the common data in result set.

For example,
We have multiple books with genera as below

Book 1 – classic
Book2 – romance
Book 3 – classic

So, If we want to count number of books with same genera, then we can use Group by clause with the SQL’s Count aggregate function resulting following output.

Classic -2
Romance -1

POINTS:

  • GROUP BY is used to group rows that is having same values.
  • Using group by, results one row from each group i.e. it reduces the number of rows in the result set.
  • Group by used with aggregate functions to group the result set.
  • Group by is used in the select statement with aggregate functions if required to produce summary reports from the database.

SQL GROUP BY Syntax

Syntax
SELECT [column_1],[column_2]……………….,aggregate_function[Column_n]
from [table_name]
[WHERE  CONDITIONS]
GROUP BY [column_1],[column_2]…………..[Column_n];

Group by Example

Count the number of book titles with same genre.

SELECT GENRE,COUNT(TITLE) FROM GENRES,BOOKS
WHERE ID=GENREID GROUP BY GENRE;

GENRECOUNT(TITLE)
NULL1
CLASSIC2
FICTION1
POETRY1
ROMANCE2

Point to Notice:

  • Group By cannot use column aliasing.
  • Group By must contain the column on which to perform the grouping operation.

Published inSQL