Master SQL GROUP BY: Organize and aggregate data

SQL GROUP BY clause: Data grouping and summary

GROUP BY clause of SQL is used to group data by the values ​​of one or more columns. It is usually used in combination with aggregate functions (such as SUM , COUNT , AVG , MAX , MIN ) to calculate each group.

GROUP BY Syntax

 <code class="language-sql">SELECT column1, aggregate_function(column2) FROM table_name GROUP BY column1;</code>

• column1 : Group by column.
• aggregate_function(column2) : The aggregate function that acts on each group.
• table_name : Data source table.

How GROUP BY works

1. Grouping: Rows with the same column1 value are divided into the same group.
2. Aggregation: Apply an aggregate function to each group to calculate a single result.

Example table: Sales data

---

Product	Category	Sales_amount	Region
Laptop	Electronics	1000	North (north)
Mobile phone (phone)	Electronics	500	South (south)
TV (TV)	Electronics	700	North (north)
Office desk (desk)	Furniture	200	East (east)
Chair	Furniture	150	East (east)

---

GROUP BY usage example

1. Total sales are calculated by category

 <code class="language-sql">SELECT category, SUM(sales_amount) AS total_sales FROM sales GROUP BY category;</code>

result:

Category	Total sales (total_sales)
Electronics	2200
Furniture	350

---

2. Statistics the number of products in each category

 <code class="language-sql">SELECT category, COUNT(product) AS product_count FROM sales GROUP BY category;</code>

result:

Category	Product quantity (product_count)
Electronics	3
Furniture	2

---

3. Group by multiple columns

 <code class="language-sql">SELECT category, region, SUM(sales_amount) AS regional_sales FROM sales GROUP BY category, region;</code>

result:

Category	Region	Regional sales (regional_sales)
Electronics	North (north)	1700
Electronics	South (south)	500
Furniture	East (east)	350

---

GROUP BY combined with HAVING

The HAVING clause is used to filter grouping results after grouping. Unlike the WHERE clause, WHERE clause filters rows before grouping.

Example: Filter categories with sales of more than 500

 <code class="language-sql">SELECT category, SUM(sales_amount) AS total_sales FROM sales GROUP BY category HAVING SUM(sales_amount) > 500;</code>

result:

Category	Total sales (total_sales)
Electronics	2200

---

GROUP BY Important Tips

1. Execution order: Grouping -> Aggregation -> HAVING Filtering.
2. SELECT column: Must appear in GROUP BY clause or be used as an argument to the aggregate function.

Valid query:

 <code class="language-sql">SELECT category, MAX(sales_amount) FROM sales GROUP BY category;</code>

Invalid query:

 <code class="language-sql">SELECT Product, SUM(Sales_Amount) FROM sales GROUP BY Category; -- 错误：Product 不在GROUP BY 中，也不是聚合函数的参数</code>

3. Multi-column grouping: You can use multiple columns to create a more detailed division.
4. NULL value processing: NULL values ​​are treated as a separate group.

Practical application scenarios

• Sales Statement: Calculate the total sales of each product or region.
• Inventory Management: Calculate the quantity of items in each category.
• Data analysis: Calculate the mean or sum by category, date, or position.

Summarize

GROUP BY clause is a powerful data aggregation tool in SQL that can effectively generate meaningful analysis results. Proficiency in the use of GROUP BY is crucial for efficient database queries.

Author: Abhay Singh Kathayat
Full-stack developer, proficient in front-end technology, and build efficient, scalable, user-friendly applications using a variety of programming languages ​​and frameworks.
Contact email: kaashshorts28@gmail.com

The above is the detailed content of Master SQL GROUP BY: Organize and aggregate data. For more information, please follow other related articles on the PHP Chinese website!