Group By Clause, SUM, MAX, MIN, AVG, COUNT and COUNT_BIG Functions in SQL Server(TSQL)

 

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.

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.

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.

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.

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.

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.

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.

We can use these functions without using GROUP BY Clause as follows:

But if we want to use it with another column without grouping, the following error will occur.

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.

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.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *