🔧 GROUP BY & DISTINCT in SQL

🕛 Zeit seit Veröffentlichung: 56 Tage, 5 Stunden 36 Minuten
📆 Veröffentlicht am: 13.10.2024 um 07:20 Uhr
Nachrichtenbereich: 🔧 Programmierung
🔗 Quelle: dev.to

Here's a detailed explanation of the difference between GROUP BY and DISTINCT, including table definition, sample data insertion, and examples.

Step 1: Create the Sales Table

CREATE TABLE Sales (
Product VARCHAR(50),
Quantity INT
);

Step 2: Insert Sample Data

INSERT INTO Sales (Product, Quantity) VALUES
('Apple', 10),
('Banana', 5),
('Apple', 8),
('Orange', 7),
('Banana', 3);

After inserting the data, the Sales table looks like this:

Explanation of GROUP BY and DISTINCT

1. GROUP BY Clause

Purpose: The GROUP BY clause is used to group rows that have the same values in specified columns. It is typically used with aggregate functions (like SUM, COUNT, AVG) to perform calculations on each group.

Behavior: It will group all the rows with the same Product value and then allow you to apply functions like SUM to calculate totals.

Example:

SELECT Product, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY Product;

Output: | Product | TotalQuantity | |---------|---------------| | Apple | 18 | | Banana | 8 | | Orange | 7 |

Explanation: The query groups the Sales data by Product, and SUM(Quantity) calculates the total quantity for each Product. Each row in the output represents a product with its total quantity sold.

1. DISTINCT Clause

Purpose: The DISTINCT clause is used to remove duplicates from the result set and return only unique values.

Behavior: It filters out duplicates and returns only one row for each unique Product.

Example:

SELECT DISTINCT Product
FROM Sales;

Output: | Product | |---------| | Apple | | Banana | | Orange |

Explanation: The DISTINCT clause ensures that each Product appears only once, without any aggregation. It filters out duplicates, showing only unique values for Product.

...