Group by, having , order by

In MySQL, GROUP BY, HAVING, and ORDER BY are clauses used in SQL queries to group, filter, and sort the result set. Here’s an explanation of each:

GROUP BY

The GROUP BY clause groups rows that have the same values into summary rows, like "find the total quantity sold for each product". It is often used with aggregate functions such as SUM(), AVG(), MAX(), MIN(), and COUNT().

HAVING

The HAVING clause is used to filter groups of rows created by the GROUP BY clause. It is similar to the WHERE clause but is used with aggregated data.

ORDER BY

The ORDER BY clause is used to sort the result set of a query by one or more columns. By default, it sorts in ascending order, but you can specify DESC to sort in descending order

Example:
-- Create Products table
CREATE TABLE Products (
    ProductID INT AUTO_INCREMENT PRIMARY KEY,
    ProductName VARCHAR(100) NOT NULL
);

-- Insert sample data into Products
INSERT INTO Products (ProductName) VALUES
('Product A'), -- ProductID 1
('Product B'), -- ProductID 2
('Product C'); -- ProductID 3

-- Create Customers table
CREATE TABLE Customers (
    CustomerID INT AUTO_INCREMENT PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL
);

-- Insert sample data into Customers
INSERT INTO Customers (CustomerName) VALUES
('Customer X'), -- CustomerID 1
('Customer Y'), -- CustomerID 2
('Customer Z'); -- CustomerID 3

-- Create Sales table
CREATE TABLE Sales (
    SaleID INT AUTO_INCREMENT PRIMARY KEY,
    ProductID INT,
    CustomerID INT,
    SaleDate DATE,
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    TotalPrice DECIMAL(10, 2),
    FOREIGN KEY (ProductID) REFERENCES Products(ProductID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert sample data into Sales
INSERT INTO Sales (ProductID, CustomerID, SaleDate, Quantity, UnitPrice, TotalPrice) VALUES
(1, 1, '2024-01-01', 2, 15.00, 30.00),
(2, 2, '2024-01-02', 1, 25.00, 25.00),
(1, 3, '2024-01-03', 3, 15.00, 45.00),
(3, 1, '2024-01-04', 4, 10.00, 40.00),
(1, 2, '2024-01-05', 1, 15.00, 15.00),
(2, 3, '2024-01-06', 2, 25.00, 50.00);


Example Queries
1. GROUP BY with ORDER BY

Get the total quantity sold for each product, ordered by total quantity sold in descending order.

SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID
ORDER BY TotalQuantity DESC;

Output:

+-----------+---------------+
| ProductID | TotalQuantity |
+-----------+---------------+
|         1 |             6 |
|         3 |             4 |
|         2 |             3 |
+-----------+---------------+

2. GROUP BY with HAVING

Get the products where the total quantity sold is greater than 3.

SELECT ProductID, SUM(Quantity) AS TotalQuantity
FROM Sales
GROUP BY ProductID
HAVING SUM(Quantity) > 3;


Output:
+-----------+---------------+
| ProductID | TotalQuantity |
+-----------+---------------+
|         1 |             6 |
|         3 |             4 |
+-----------+---------------+

3.GROUP BY with HAVING and ORDER BY
Get the products where the total quantity sold is greater than 3, ordered by total sales amount in descending order.
SELECT ProductID, SUM(Quantity) AS TotalQuantity, SUM(TotalPrice) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(Quantity) > 3
ORDER BY TotalSales DESC;

Output:

+-----------+---------------+
| ProductID | TotalQuantity |
+-----------+---------------+
|         1 |             6 |
|         3 |             4 |
+-----------+---------------+

4. GROUP BY with multiple columns

Get the total sales amount for each product per customer.
SELECT ProductID, CustomerID, SUM(TotalPrice) AS TotalSales
FROM Sales
GROUP BY ProductID, CustomerID
ORDER BY ProductID, CustomerID;

Output:

+-----------+------------+------------+
| ProductID | CustomerID | TotalSales |
+-----------+------------+------------+
|         1 |          1 |      30.00 |
|         1 |          2 |      15.00 |
|         1 |          3 |      45.00 |
|         2 |          2 |      25.00 |
|         2 |          3 |      50.00 |
|         3 |          1 |      40.00 |
+-----------+------------+------------+

Comments

Post a Comment

Popular posts from this blog

KTU DBMS LAB CSL 333 BTech S5 - Dr Binu V P

 About Me About the course Syllabus and Experiments **************************************** Introduction to RDBMS Working with MySQL ER Diagrams-MySQL Workbench Introduction to SQL Data types in MySQL Built-in Functions MySQL Aggregate Functions Enforcing rules using constraints MySQL -Lab Exercise ****************************************** Joining tables Order by, group by and having clause Nested Queries MySQL -Lab Exercise ******************************************* Insert Data from a CSV file Set Operations Queries using temporary tables TCL commands in MySQL DCL commands in MySQL Views and Assertions Library Database- Lab Exercise ********************************************* PL/SQL and Stored Procedures IF statement in PL/SQL CASE statement in PL/SQL Loops in PL/SQL Procedures in PL/SQL Functions in PL/SQL Triggers in PL/SQL Cursors in PL/SQL Packages in PL/SQL Exception Handling in PL/SQL Backing up and Restoring Databases Query Analysis using Query Plan ***********************
Read more

KTU DBMS LAB CSL 333 Syllabus and Experiments

 Syllabus 1. Design a database schema for an application with ER diagram from a problem description **. 2. Creation, modification, configuration, and deletion of databases using UI and SQL Commands **. 3. Creation of database schema - DDL (create tables, set constraints, enforce relationships, create indices, delete and modify tables). Export ER diagram from the database and verify relationships** (with the ER diagram designed in step 1). 4. Database initialization - Data insert, Data import to a database (bulk import using UI and SQL Commands)**. 5. Practice SQL commands for DML (insertion, updating, altering, deletion of data, and viewing/querying records based on condition in databases)**. 6. Implementation of built-in functions in RDBMS**. 7. Implementation of various aggregate functions in SQL**. 8. Implementation of Order By, Group By & Having clause **. 9. Implementation of set operators nested queries, and join queries **. 10. Implementation of queries using temp tables. 11
Read more

Creating a Database and Table ,executing queries - MySQL Lab Exercise

Create a database school and use it CREATE DATABASE school; USE school; Let's create a simple table schema for a students table in MySQL, followed by a set of queries to interact with the data. id : An integer that uniquely identifies each student (primary key). first_name : The student's first name. last_name : The student's last name. birthdate : The student's birth date. email : The student's email address. gender : The student's gender. enrollment_date : The date when the student was enrolled. CREATE TABLE students (     id INT AUTO_INCREMENT PRIMARY KEY,     first_name VARCHAR(50) NOT NULL,     last_name VARCHAR(50) NOT NULL,     birthdate DATE NOT NULL,     email VARCHAR(100) NOT NULL,     gender ENUM('Male', 'Female', 'Other') NOT NULL,     enrollment_date DATE NOT NULL ); check the schema desc students; Inserting Sample Data INSERT INTO students (first_name, last_name, birthdate, email, gender, enrollment_date) VALUES ('John&
Read more