Sql Group By clause

Sql Group By clause

Blog Bustle 28/03/2020

Sql Group By clause is used mostly with Sql aggregate functions(COUNT, SUM, AVG, MIN, MAX) to group rows by one or more columns. Sql Group By clause is used with select sql query. We use sql Group By clause after WHERE clause and before ORDER BY clause. WHERE clause is used to filter sql query result and we use Sql Having clause after Group By clause to exclude records.

Sql Group By clause syntax

SELECT * from table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s) ASC|DESC;

Sql Group By clause examples

Let's look at some examples to understand Sql Group By clause.

I have student fees table.

id name class admission_fee
1 justin 6 600
2 robin 7 700
3 jessie 6 600
4 chris 8 800
5 peter 7 700

I want to find total admission fee of each class and sql query will be

SELECT class,SUM(admission_fee)
from student_fees
GROUP BY class;

After running this sql query, result will be

class SUM(admission_fee)
6 1200
7 1400
8 800

I want to find total admission fee of class 6th and 7th and sql query will be

SELECT class,SUM(admission_fee)
from student_fees
WHERE class IN(6,7)
GROUP BY class;

or

SELECT class,SUM(admission_fee)
from student_fees
GROUP BY class
HAVING class IN(6,7);

After running this sql query, result will be

class SUM(admission_fee)
6 1200
7 1400

I want to find total admission fee of class 6th and 7th if their total admission fee is greater than 1200 and sql query will be

SELECT class,SUM(admission_fee)
from student_fees
WHERE class IN(6,7)
GROUP BY class
HAVING SUM(admission_fee) > 1200;

After running this sql query, result will be

class SUM(admission_fee)
7 1400

Error: #1054 - Unknown column 'id' in 'having clause'

We can use only those columns, functions in having clause whom we want to fetch otherwise we will get error 1054 unknown column in having clause. In last one query we can use only class column and SUM('admission_fee') function in having clause.

After looking at these examples, we can easily understand and use sql group by clause. Hope you like this tutorial.

Other important links

Sql Between operator

Sql Limit clause

Sql Order By clause

Sql Aggregate Functions

Sql IN operator

Sql Group By clause searches:

Tags: #mysql