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;

Point to Notice:

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