hi, today I discuss about important clauses - GROUP BY & HAVING .
GROUP BY:
GROUP BY is used to collect data from multiple records and group the results by one or more columns.
GROUP BY clause is used with SELECT statement Syntax:
SELECT expression1, expression2, ... expression_n,
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n; Example :
Consider students table from our previous posts. now apply GROUP BY on student_city.
SELECT student_city
FROM students
GROUP BY student_city;
It print the all city in a group.
GROUP BY with Aggregate functions :
The GROUP BY clause is often used with aggregate functions such as AVG(), COUNT(), MAX(), MIN() and SUM(). In this case, the aggregate function returns the summary information per group. For example, given groups of products in several categories, the AVG() function returns the average price of products in each category.
Example:
SELECT student_city, COUNT(student_city) as number_of_city
FROM students
GROUP BY student_city;It will display the number of city.
HAVING Clause
The HAVING clause is an optional clause of the SELECT statement. It is used to filter groups of rows returned by the GROUP BY clause. This is why the HAVING clause is usually used with the GROUP BY clause.
Syntax:
SELECT expression1, expression2, ... expression_n,
aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n
HAVING having_condition; Example:
SELECT student_city, COUNT(student_city) as number_of_city
FROM students
GROUP BY student_city
HAVING COUNT(student_city) >= 2;Here we can make condition with Group By clause.
It will display the number of city count which is greater than and equals to 2.
Thank you for reading .Feel Free to ask you Query in comment section.