Master SQL GROUP BY: Organize and aggregate data
Apr 08, 2025 pm 06:36 PM
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="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
- Grouping: Rows with the same
column1value are divided into the same group. - 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="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="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="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="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
- Execution order: Grouping -> Aggregation ->
HAVINGFiltering. -
SELECTcolumn: Must appear inGROUP BYclause or be used as an argument to the aggregate function.
Valid query:
<code class="sql">SELECT category, MAX(sales_amount) FROM sales GROUP BY category;</code>
Invalid query:
<code class="sql">SELECT Product, SUM(Sales_Amount) FROM sales GROUP BY Category; -- 錯誤：Product 不在GROUP BY 中，也不是聚合函數(shù)的參數(shù)</code>
- Multi-column grouping: You can use multiple columns to create a more detailed division.
- NULL value processing:
NULLvalues ??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!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
What is Ethereum? What are the ways to obtain Ethereum ETH?
Jul 31, 2025 pm 11:00 PM
Ethereum is a decentralized application platform based on smart contracts, and its native token ETH can be obtained in a variety of ways. 1. Register an account through centralized platforms such as Binance and Ouyiok, complete KYC certification and purchase ETH with stablecoins; 2. Connect to digital storage through decentralized platforms, and directly exchange ETH with stablecoins or other tokens; 3. Participate in network pledge, and you can choose independent pledge (requires 32 ETH), liquid pledge services or one-click pledge on the centralized platform to obtain rewards; 4. Earn ETH by providing services to Web3 projects, completing tasks or obtaining airdrops. It is recommended that beginners start from mainstream centralized platforms, gradually transition to decentralized methods, and always attach importance to asset security and independent research, to
BTC digital currency account registration tutorial: Complete account opening in three steps
Jul 31, 2025 pm 10:42 PM
First, select well-known platforms such as Binance Binance or Ouyi OKX, and prepare your email and mobile phone number; 1. Visit the official website of the platform and click to register, enter your email or mobile phone number and set a high-strength password; 2. Submit information after agreeing to the terms of service, and complete account activation through the email or mobile phone verification code; 3. Complete identity authentication (KYC) after logging in, enable secondary verification (2FA) and check security settings regularly to ensure account security. After completing the above steps, you can successfully create a BTC digital currency account.
Ethena treasury strategy: the rise of the third empire of stablecoin
Jul 30, 2025 pm 08:12 PM
The real use of battle royale in the dual currency system has not yet happened. Conclusion In August 2023, the MakerDAO ecological lending protocol Spark gave an annualized return of $DAI8%. Then Sun Chi entered in batches, investing a total of 230,000 $stETH, accounting for more than 15% of Spark's deposits, forcing MakerDAO to make an emergency proposal to lower the interest rate to 5%. MakerDAO's original intention was to "subsidize" the usage rate of $DAI, almost becoming Justin Sun's Solo Yield. July 2025, Ethe
What is Binance Treehouse (TREE Coin)? Overview of the upcoming Treehouse project, analysis of token economy and future development
Jul 30, 2025 pm 10:03 PM
What is Treehouse(TREE)? How does Treehouse (TREE) work? Treehouse Products tETHDOR - Decentralized Quotation Rate GoNuts Points System Treehouse Highlights TREE Tokens and Token Economics Overview of the Third Quarter of 2025 Roadmap Development Team, Investors and Partners Treehouse Founding Team Investment Fund Partner Summary As DeFi continues to expand, the demand for fixed income products is growing, and its role is similar to the role of bonds in traditional financial markets. However, building on blockchain
Ethereum (ETH) NFT sold nearly $160 million in seven days, and lenders launched unsecured crypto loans with World ID
Jul 30, 2025 pm 10:06 PM
Table of Contents Crypto Market Panoramic Nugget Popular Token VINEVine (114.79%, Circular Market Value of US$144 million) ZORAZora (16.46%, Circular Market Value of US$290 million) NAVXNAVIProtocol (10.36%, Circular Market Value of US$35.7624 million) Alpha interprets the NFT sales on Ethereum chain in the past seven days, and CryptoPunks ranked first in the decentralized prover network Succinct launched the Succinct Foundation, which may be the token TGE
Solana and the founders of Base Coin start a debate: the content on Zora has 'basic value'
Jul 30, 2025 pm 09:24 PM
A verbal battle about the value of "creator tokens" swept across the crypto social circle. Base and Solana's two major public chain helmsmans had a rare head-on confrontation, and a fierce debate around ZORA and Pump.fun instantly ignited the discussion craze on CryptoTwitter. Where did this gunpowder-filled confrontation come from? Let's find out. Controversy broke out: The fuse of Sterling Crispin's attack on Zora was DelComplex researcher Sterling Crispin publicly bombarded Zora on social platforms. Zora is a social protocol on the Base chain, focusing on tokenizing user homepage and content
What is Huobi HTX red envelope? How to send and receive red envelopes? Huobi divides 1000U activities
Jul 30, 2025 pm 09:45 PM
Table of Contents 1. What is Huobi HTX red envelope? 2. How to create and send red envelopes? 3. How to receive red envelopes? 1. Receive password red envelopes 2. Scan the QR code to receive red envelopes 3. Click on the red envelope link to receive red envelopes 4. Check the red envelopes and share more instructions: 1. What is Huobi HTX red envelope? Huobi HTX red envelopes support users to send cryptocurrencies to friends in the form of red envelopes. You can create cryptocurrency red envelopes with random or fixed amounts, and send them to friends by sending red envelope passwords, sharing links or posters. Your friends can receive it for free in Huobi HTXAPP or click on the link. Huobi HTX red envelopes also support unregistered users to receive them, and
What is Zircuit (ZRC currency)? How to operate? ZRC project overview, token economy and prospect analysis
Jul 30, 2025 pm 09:15 PM
Directory What is Zircuit How to operate Zircuit Main features of Zircuit Hybrid architecture AI security EVM compatibility security Native bridge Zircuit points Zircuit staking What is Zircuit Token (ZRC) Zircuit (ZRC) Coin Price Prediction How to buy ZRC Coin? Conclusion In recent years, the niche market of the Layer2 blockchain platform that provides services to the Ethereum (ETH) Layer1 network has flourished, mainly due to network congestion, high handling fees and poor scalability. Many of these platforms use up-volume technology, multiple transaction batches processed off-chain
