SQL GROUP BY Clause
The SQL GROUP BY statement is used in conjunction with the aggregate functions to arrange identical data into groups.
The SQL GROUP BY statement is used to collect data across multiple records and group the result-set by one or multiple columns.
In the SQL GROUP BY statement must contains a aggregate function in the SQL query.
The SQL GROUP BY statement can be applied in any valid SQL SELECT statement.
Search Keys
- sql group by clause
- sql group by
- sql group by order by
- select group by
- group by where
- sql server group by
- group by clause in sql
- mssql group by
- t sql group by
- sql group by example
- sql case group by
- group by select
- group by 2 columns
- multiple group by sql
- sql update group by
- group by expression
- order by group by sql
- where and group by
- group by sql example
- sql group by multiple columns
- sql group by statement
- sql group by date
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
BookId |
BookName |
BookPrice |
RelYear |
DomainName |
AuthorName |
1 |
Oracle Interview Questions |
120 |
2015 |
Optimization |
Balan |
2 |
SQL Puzzles & Answers |
136.33 |
2014 |
Database |
Balan |
3 |
The Microsoft Data Warehouse |
84.22 |
2015 |
Performance |
Siva Kumar |
4 |
Jump Start MySQL |
178.69 |
2012 |
Performance |
Padmavathi |
5 |
Head First SQL |
150 |
2008 |
Performance |
Balan |
6 |
Pro SQL Azure |
200 |
2015 |
Optimization |
Keshavan |
7 |
Practical SQL |
130 |
2011 |
Performance |
Harish Karthik |
8 |
SQL Server Fundamentals |
160 |
2012 |
Performance |
Devi Mai |
9 |
SQL in Easy Steps |
70 |
2014 |
Database |
Azaghu Varshith |
10 |
Simply MySql |
135 |
2007 |
Security |
Pandurengan |
11 |
Sql Server Cookbook |
95 |
2006 |
Performance |
Varshini Kutty |
12 |
Simply SQL Server |
168.27 |
2006 |
Database |
Rishi Keshan |
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:
DomainName |
Total Price |
Database |
374.6 |
Optimization |
320 |
Performance |
797.91 |
Security |
135 |
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:
DomainName |
Total Price |
Database |
168.27 |
Performance |
375 |
Security |
135 |
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:
DomainName |
RelYear |
Total Price |
Database |
2014 |
206.33 |
Performance |
2011 |
130 |
Performance |
2012 |
338.69 |
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:
DomainName |
RelYear |
Total Price |
Performance |
2011 |
130 |
Performance |
2012 |
338.69 |
Database |
2014 |
206.33 |