Examples of DISTINCT and GROUP BY in SQL
Let’s take a closer look at the functionality and use cases of GROUP BY and DISTINCT using a simple example in MySQL.
Sales Table:
-- Create sales table
CREATE TABLE sales(
OrderID INT PRIMARY KEY,
Product VARCHAR,
Name VARCHAR(50),
Price INT
);
To add the value to the sales table:
-- Insert data into sales table
INSERT INTO sales (OrderID,Product, Name, Price) VALUES
(1, 'Laptop','John', 1200),
(2, 'Smartphone','Alice', 800),
(3, 'Tablet','John', 500),
(4, 'Laptop','Bob', 1200),
(5, 'Laptop','Alice', 1200),
(6, 'Tablet','Emily' 400);
DISTINCT keyword
Example 1: Unique Products from Sales Table
SELECT DISTINCT Product FROM sales;
Output:
Explanation: The above result shows the unique values in the product column i.e. without duplicates.
Example 2: Find a the DISTINCT product and price
SELECT DISTINCT Product, Price FROM sales;
Output:
Explanation: The DISTINCT keyword can be used with multiple columns but it is recommended to use it on necessary columns to obtain the desired result.
GROUP BY clause
Example 1: Find the total sales of each product
SELECT Product ,SUM(Price) FROM sales GROUP BY Product;
Output:
Explanation: This SQL query retrieves the total sales for each unique product from the “sales” table. The output displays product names alongside their corresponding aggregated sum of prices.
Example 2: Find the total sales and quantity of each product
SELECT Product, SUM(price) as total_sales, COUNT(*) as quantity FROM Sales GROUP BY Product;
Output:
Explanation: This SQL query calculates the total sales and quantity for each unique product in the “Sales” table. The output presents product names, their total sales, and the respective quantities.
DISTINCT vs GROUP BY in SQL
SQL (Structured Query Language) is used to manage and manipulate the data in relational databases. It can be used for tasks such as database querying, data editing, database and table creation and deletion, and granting user permissions.
We can use the DISTINCT keyword and GROUP BY clause when we want to obtain the unique records of a table in SQL. Even though their purpose is the same, they are used in various ways and they also function differently.