Group by SQL function || Group by in SQL with examples

Group by SQL function

Group by in SQL is a function by which we are grouping column values against record groups. It helps in aggregating columns data and getting summary of values. Group by SQL function is a very useful and frequently used SQL function.

• The GROUP BY clause groups records into summary rows.
• GROUP BY returns one record for each group.
• GROUP BY is used with aggregrates like COUNT, MAX, SUM, SUM and AVERAGE.

Example for Group by in SQL

In the below example SQL GROUP BY count we used SQL GROUP BY clause to find out COUNT of JOB_ID in the EMPLOYEES table.

We used SELECT STATEMENT to get data from table, then we used JOB_ID column to group data. All values from JOB_ID will be in records(rows). Then we used COUNT(*) which is an aggregate function to get counts of records against the values of JOB_ID column. Next, we used SQL Table name from where we have used column name to group by our data. after this we use GROUP BY clause to instruct SQL to group the data. And lastly the column name again.

SQL GOUP BY SYNTAX

SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names;

GROUP BY SYNTAX with ORDER BY

SELECT column-names
FROM table-name
WHERE condition
GROUP BY column-names
ORDER BY column-names;

SQL GROUP BY with more columns

SELECT DEPARTMENT_ID,min(SALARY) MIN_SALARY,MAX(SALARY) MAX_SALARY,AVG(SALARY) AVG_SALARY  FROM EMPLOYEES
GROUP BY DEPARTMENT_ID;

In the above group by oracle example we used three columns as group by. In the first column we took maximum salary then minimum salary and in the last average salary. We group by it by DEPARTMENT_ID. So the final result shows unique values from department_ids in EMPLOYEES table and minimum, maximum and average salary of these department_ids.

SQL GROUP BY HAVING

SELECT
    department_id,
    MIN(salary) min_salary,
    MAX(salary) max_salary,
    AVG(salary) avg_salary
FROM
    employees
GROUP BY
    department_id
    having avg(salary) > 5000;

In the above SQL query example, we used HAVING CLAUSE to filter data our data. When we mention HAVING it means that value greater than 5000 should be coming from SQL query.

You can watch the below video to get more clarity on SQL group by function.

You can read more about SQL from here.

You can read more about GROUP BY from this link as well.