Meritshot Tutorials
SQL Tutorial
-
SQL SyntaxSQL Syntax
-
SQL ORDER BY ClauseSQL ORDER BY Clause
-
Introduction to SQLIntroduction to SQL
-
SQL SELECT DISTINCTSQL SELECT DISTINCT
-
SQL Logical OperatorsSQL Logical Operators
-
SQL WHERE ClauseSQL WHERE Clause
-
SQL SELECT StatementSQL SELECT Statement
-
SQL DELETE StatementSQL DELETE Statement
-
SQL INSERT INTOSQL INSERT INTO
-
SQL Null ValuesSQL Null Values
-
SQL Update StatementSQL Update Statement
-
SQL Select TopSQL Select Top
-
SQL Aggregate FunctionsSQL Aggregate Functions
-
SQL LIKE and WildcardsSQL LIKE and Wildcards
-
SQL IN and SQL BETWEENSQL IN and SQL BETWEEN
-
SQL JOINSSQL JOINS
-
SQL Group BySQL Group By
-
SQL HavingSQL Having
-
SQL EXISTSSQL EXISTS
-
SQL SELECT INTOSQL SELECT INTO
-
SQL INSERT INTO SELECTSQL INSERT INTO SELECT
SQL GROUP BY
The GROUP BY clause in SQL is used to group rows that have the same values in specified columns into summary rows. It is often used with aggregate functions like SUM(), COUNT(), AVG(), MIN(), and MAX() to perform operations on grouped data.
Purpose
- Aggregate Data: Allows you to aggregate data based on one or more columns.
- Summarize Information: Provides a way to summarize or categorize data, often used in reporting and analysis.
Syntax:
SELECT column_name(s), aggregate_function(column_name)
FROM table_name
GROUP BY column_name(s);
Example:
Inserting a new record into the employees table with columns: employee_id, first_name, last_name, department, salary, and age.
1 Count of Employees in Each Department
This query retrieves the number of employees in each department.
SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id;
Output:
department_id | num_employees |
1 | 10 |
2 | 5 |
3 | 8 |
2. Average Salary by Department
This query retrieves the number of employees in each department.
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id;
Output:
department_id | avg_salary |
1 | 45000 |
2 | 38000 |
3 | 50000 |
3. Total Sales by Product
This query retrieves the total sales amount for each product.
SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;
Output:
product_id | total_sales |
101 | 150000 |
102 | 200000 |
103 | 175000 |
4. Maximum and Minimum Order Amounts
This query finds the maximum and minimum order amounts for each customer.
SELECT customer_id, MAX(order_amount) AS max_order, MIN(order_amount) AS min_order
FROM orders
GROUP BY customer_id;
Output:
customer_id | max_order | min_order |
1 | 1200 | 300 |
2 | 800 | 150 |
3 | 900 | 250 |
Tips to Remember
- Column Inclusion: All columns in the SELECT clause that are not used with aggregate functions must be included in the GROUP BY clause.
- Order of Grouping: The GROUP BY clause must be placed after the WHERE clause but before the ORDER BY clause in a SQL query.
- Aggregate Functions: Common aggregate functions used with GROUP BY include SUM(), COUNT(), AVG(), MIN(), and MAX().
- Grouping Multiple Columns: You can group by multiple columns to create a more granular summary.
Power of GROUP BY
- Summarize Data: Allows for detailed aggregation of data, providing insight into various metrics.
- Flexibility: Enables complex queries by combining multiple grouping levels.
- Efficient Reporting: Facilitates efficient data reporting and analysis.
Frequently Asked Questions
Q1: Can I use GROUP BY without aggregate functions?
A1: Yes, you can use GROUP BY without aggregate functions. In such cases, it groups the rows based on the specified columns and returns a single row for each group.
Example:
SELECT department_id
FROM employees
GROUP BY department_id;
Q2: Can I use HAVING with GROUP BY?
A2: Yes, HAVING is used to filter groups created by GROUP BY based on a condition. It works similarly to the WHERE clause but operates on aggregated data.
Example:
SELECT department_id, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id
HAVING COUNT(employee_id) > 5;
Q3: Can I sort results after grouping?
A3: Yes, you can use the ORDER BY clause to sort the results after grouping.
Example:
SELECT department_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY department_id
ORDER BY avg_salary DESC;
Q4: How do I group by multiple columns?
A4: You can specify multiple columns in the GROUP BY clause to group by more than one column.
Example:
SELECT department_id, job_title, COUNT(employee_id) AS num_employees
FROM employees
GROUP BY department_id, job_title;
Q5: Can GROUP BY be used with JOIN clauses?
A5: Yes, GROUP BY can be used in conjunction with JOIN clauses to aggregate data from multiple tables.
Example:
SELECT d.department_name, COUNT(e.employee_id) AS num_employees
FROM employees e
INNER JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name;
