SQL GROUP BY CLAUSE
We have already learned WHERE Clause and ORDER BY Clause and TOP Clause and SELECT DISTINCT Clause in our previous post.
In this sql post i will explain you what is GROUP BY Clause and How to use GROUP BY Clause in sql with example.
GROUP BY Clause – The SQL GROUP BY Clause is used to returns all records togather with specified column and perfom aggregate function on one or more columns.
The SQL GROUP BY Clause Syntax :
SELECT col1, aggregate_function(col2) FROM table_name GROUP BY clo1
Above sql statement the aggregate function may be SUM, AVG, COUNT, MAX, MIN.
The aggregate function performed on col2 with group by col1 value
The SQL GROUP BY Clause Example :
Here, we have sql table Student with some records like below.
The SQL Student Table :
ID | Name | City | Fees | |
---|---|---|---|---|
1 | Meera | Bombay | meera@yahoo.com | 2000 |
2 | Ram | Surat | ram@yahoo.com | 5000 |
3 | Jay | Bombay | jay@yahoo.com | 3000 |
4 | Vaidehi | Ahmedabad | meera@yahoo.com | 7000 |
5 | Dharva | Ahmedabad | dharva@yahoo.com | 3000 |
Now, we want to get number of student by city name wise, the sql group by query would be like:
SELECT City, COUNT(ID) as Total_Student FROM Student GROUP BY City
The SQL GROUP BY Clause Result is :
City | Total_Student |
---|---|
Bombay | 2 |
Surat | 1 |
Ahmedabad | 2 |
Example : Sum of total Fees given by student city name wise.
SELECT City, SUM(Fees) as Total_Fees FROM Student GROUP BY City
The SQL GROUP BY Query Result is :
City | Total_Fees |
---|---|
Bombay | 5000 |
Surat | 5000 |
Ahmedabad | 10000 |