GROUP BY and HAVING Statements | SQL-MySQL | Belayet Hossain

?? GROUP BY in SQL

The GROUP BY statement in SQL is used to arrange identical data into groups. It is typically used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform operations on each group of data.

→ Syntax:

select column1, aggregate_function

from table_name

where filter_condiction

group by column1

order by column1 asc;

→ Example 1 : Find the employee quantity of each department from the 'Employee table'.

→ Query:

→ Query with Sub Query:

→ Output:

→ Example 2 : Find the employee quantity of 'Engineering' department from the 'Employee table' using SUB Query.

→ Query:

→ Output:

→ Example 2 : Find the number of employees based on the Supervisor and Section from the employee_salary table. (Group by on the 2 columns)

→ Query :

→ Output :

?? HAVING Clause in SQL:

The HAVING clause is used to filter the results of a GROUP BY operation based on conditions applied to aggregate functions like SUM(), AVG(), COUNT(), etc. It works similarly to the WHERE clause but applies to groups of rows rather than individual rows.

→ Syntax:

select column1, aggregate_function

from table_name

where filetr_condition

group by column1

having aggreagte_fucntion, filter_condition

order by column1 asc ;

→ Example 4: Find the total salary amount for each department, excluding the 'eng' department, and only include departments where the total salary is above 50,000 using the 'employee_dept' table.

→ Data table:

→ Query:

→ Output:

??GROUP BY, HAVING, and WHERE in SQL

These three SQL clauses are used for different purposes in data filtering and aggregation:

1. WHERE : Filters rows before grouping occurs.
2. GROUP BY: Groups rows into summary rows based on column values.
3. HAVING: Filters groups after aggregation.

?? Key Difference:

#dataanalytics #dataanalyst #SQL #MySQL #powerbi #GroupBY #Having #Where #job #career #hiring #data