Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
When reading data from the database, we can use GROUP BY clause when we want to group data and process on the groups we specify.
Let’s make examples for a better understanding of Group By clause.
Example:
First, we create a table with the help of the following script and add a few records into this table.
1 2 3 4 5 6 7 8 9 10 | USE [TestDB] GO CREATE TABLE [dbo].[ProductTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [ProductName] [varchar](250) NOT NULL, [ProductPrice] [int] NOT NULL ) ON [PRIMARY] GO INSERT INTO [dbo].[ProductTable] VALUES ('Television',3400), ('Television',4300),('Phone',6700),('Phone',9800) |
Now with GROUP BY Clause, let’s write a few sample queries about the records in this table.
GROUP BY CLAUSE and SUM Function:
We group the data by ProductName and calculate the total price of the products in the same group with the following query.
1 2 | SELECT [ProductName],SUM(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
GROUP BY CLAUSE and MAX Function:
We group the data according to ProductName and find the the highest product price within the groups with the following query.
1 2 | SELECT [ProductName],MAX(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
GROUP BY CLAUSE and MIN Function:
We group the data according to ProductName and find the lowest product price within the groups with the following query.
1 2 | SELECT [ProductName],MIN(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
GROUP BY CLAUSE and AVG Function:
We group the data according to ProductName and find the average product price within the groups with the following query.
1 2 | SELECT [ProductName],AVG(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
GROUP BY CLAUSE and COUNT Function:
Aşağıdaki sorgu ile verileri ProductName’e göre gruplayarak grupladaki ürün sayısını buluyoruz.
We group the data according to ProductName and find the number of products in the groups with the following query.
1 2 | SELECT [ProductName],COUNT(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
GROUP BY CLAUSE and COUNT_BIG Function:
The COUNT_BIG function performs the same function as the COUNT function. The only difference is that the COUNT_BIG function returns the BIGINT value while the COUNT function returns int value.
We group the data according to ProductName and find the number of products in the groups with the following query.
1 2 | SELECT [ProductName],COUNT_BIG(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
We can use these functions without using GROUP BY Clause as follows:
1 | SELECT COUNT(ProductPrice) FROM [dbo].[ProductTable] |
But if we want to use it with another column without grouping, the following error will occur.
1 | SELECT [ProductName],COUNT(ProductPrice) FROM [dbo].[ProductTable] |
Msg 8120, Level 16, State 1, Line 14
Column ” is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Even if we use GROUP BY, we get this error again if we type a column that we did not specify in GROUP BY without using one of the functions above.
1 2 | SELECT ID,[ProductName],COUNT_BIG(ProductPrice) FROM [dbo].[ProductTable] GROUP BY [ProductName] |
Msg 8120, Level 16, State 1, Line 14
Column ” is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
