SQL GROUP BY Clause

Sql GROUP BY Syntax

The basic syntax is:

SELECT column_name1, column_name2, aggregate_function(column_name)
FROM table_name
GROUP BY column_name1, column_name2;

The order of group by statement will look the below statement:

SELECT column_name1, column_name2, aggregate_function(column_name)
FROM table_name
[WHERE condition]
GROUP BY column_name1, column_name2
[ORDER BY column_name1, column_name2];

Note:

• In every GROUP BY statement must contain atleast one aggregate function.
• You can group only the column which you listed in SELECT statement.

Sample Database Table - Books

SQL GROUP BY Example

The following SQL statement will calculates total price into "DomainName" wise from the "Books" table:

SELECT DomainName, SUM(BookPrice) As 'Total Price' 
FROM Books 
GROUP BY DomainName;

The result of above query is:

The following SQL statement will calculates total price into "DomainName" wise and select records that has "RelYear" < 2012 from the "Books" table:

SELECT DomainName, SUM(BookPrice) As 'Total Price' 
FROM Books 
WHERE RelYear < 2012
GROUP BY DomainName;

The result of above query is:

SQL GROUP BY More Than One Column Example

The following SQL statement will calculates total price into "DomainName" and "RelYear" wise and select records that has "RelYear" greater than "2010" and less than "2015" from the "Books" table:

SELECT DomainName, RelYear, SUM(BookPrice) As 'Total Price' 
FROM Books
WHERE RelYear > 2010 AND RelYear < 2015
GROUP BY DomainName, RelYear;

The result of above query is:

The following SQL statement will calculates total price into "DomainName" and "RelYear" wise, than order the result by "RelYear" column from the "Books" table:

SELECT DomainName, RelYear, SUM(BookPrice) As 'Total Price' 
FROM Books
WHERE RelYear > 2010 AND RelYear < 2015
GROUP BY DomainName, RelYear
ORDER BY RelYear;

The result of above query is: