Introduction

In this blog, we will discuss how to work with GROUP BY, WHERE, and HAVING clauses in SQL and explain the concept with an example in a simple way. I hope this is very useful for beginners and intermediates to help them understand the basic concept.

Group by clause

The Group by clause is often used to arrange identical duplicate data into groups with a select statement to group the result-set by one or more columns. This clause works with the select specific list of items, and we can use HAVING, and ORDER BY clauses. Group by clause always works with an aggregate function like MAX, MIN, SUM, AVG, COUNT.

Let us discuss group by clause with an example. We have a VehicleProduction table and there are some models with a price and it has some duplicate data. We want to categorize this data in a different group with a respective total price.

Example

1. Create  table  VehicleProduction
2. (
3. Id int  primary  key  Identity,
4. Model varchar (50),
5. Price money
6. )
8. Insert  into  VehicleProduction values ( ‘L551’ , 850000),( ‘L551’ , 850000),( ‘L551’ , 850000),( ‘L551’ , 750000),
9. ( ‘L538’ , 650000),( ‘L538’ , 650000),( ‘L538’ , 550000),( ‘L530’ , 450000),( ‘L530’ ,350000), ( ‘L545’ , 250000)
11. Select  * from  VehicleProduction

Output

Aggregate Functions

**MAX()- **function returns the maximum value of the numeric column of specified criteria.

Example

1. Select  max (Price) As   ‘MaximumCostOfModel’  from  VehicleProduction

Output

MIN()- function returns the minimum of the numeric column of specified criteria.

Example

1. Select  Min (Price) As   ‘MinimumCostOfModel’  from  VehicleProduction

Output

**SUM()- **function returns the total sum of a numeric column of specified criteria.

Example

1. Select  SUM(Price) As   ‘SumCostOfAllModel’  from  VehicleProduction

Output

**AVG()- **function returns the average value of a numeric column of specified criteria.

#sql