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

Search Keys

  • multiple group by
  • sql server group by having
  • sql query group by count
  • group by having example
  • sql server group by example
  • sql query group by having
  • sql group by count example
  • sql group by having example
  • group by column
  • sql group by explained
  • sql query count group by
  • sql group count
  • group functions in sql
  • sql group functions
  • sql group join
  • having sql
  • having clause in sql
  • where having sql
  • having sql example
  • sql count records
  • sql command having
  • sql query having count