Scribd
Upload
3 views7 pages

04 Perform Aggregate Functions in MySQL

This document demonstrates how to use aggregate functions in MySQL to summarize and analyze data sets. It outlines the steps to log in to the MySQL database, create a database and table, and utilize functions such as COUNT, SUM, AVERAGE, MINIMUM, and MAXIMUM. The document includes SQL code examples for each function to facilitate understanding and implementation.

Uploaded by

Prince
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views7 pages

04 Perform Aggregate Functions in MySQL

This document demonstrates how to use aggregate functions in MySQL to summarize and analyze data sets. It outlines the steps to log in to the MySQL database, create a database and table, and utilize functions such as COUNT, SUM, AVERAGE, MINIMUM, and MAXIMUM. The document includes SQL code examples for each function to facilitate understanding and implementation.

Uploaded by

Prince
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 7

Lesson 04 Demo 04

Perform Aggregate Functions in MySQL

Objective: To demonstrate how to use aggregate functions in summarizing and


analyzing data sets in MySQL

Tools required: MySQL Workbench

Prerequisites: None

Steps to be followed:
1. Log in to the MySQL Database
2. Create a Database and Table
3. Use the COUNT function
4. Use the SUM function
5. Use the AVERAGE function
6. Use the MINIMUM function
7. Use the MAXIMUM function

Step 1: Log in to the MySQL Database

1.1 Connect to your MySQL server using a MySQL client or command-line


interface

Step 2: Create a Database and Table

2.1 Create the sales_database by executing the following SQL statement and
using the Database
SQL Code:
CREATE DATABASE IF NOT EXISTS sales_database;
USE sales_database;
2.2 Create the sales_info table within the sales_database with columns for
order_id INT PRIMARY KEY, product_name VARCHAR(100), quantity INT, and
unit_price DECIMAL(10, 2)
SQL Code:
CREATE TABLE IF NOT EXISTS sales_info (
order_id INT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT,
unit_price DECIMAL(10, 2)
);
2.3 Insert sample data into the sales_info table:
SQL Code:
INSERT INTO sales_info (order_id, product_name, quantity, unit_price)
VALUES
(1, 'Product A', 10, 25.50),
(2, 'Product B', 5, 15.75),
(3, 'Product A', 8, 25.50),
(4, 'Product C', 12, 30.00),
(5, 'Product B', 7, 15.75);
Step 3: Use the COUNT function

3.1 Count the total number of orders in the sales_info table using the COUNT()
function
SQL Code:
SELECT COUNT(order_id) AS total_orders FROM sales_info;
Step 4: Use the SUM function

4.1 Calculate the total quantity and total revenue (quantity * unit_price) in the
sales_info table using the SUM() function
SQL Code:
SELECT SUM(quantity) AS total_quantity, SUM(unit_price) AS total_revenue
FROM sales_info;

Step 5: Use the AVERAGE function

5.1 Calculate the average quantity and average unit price in the sales_info
table using the AVG() function
SQL Code:
SELECT AVG(quantity) AS average_quantity, AVG(unit_price) AS
average_unit_price FROM sales_info;
Step 6: Use the MINIMUM function

6.1 Find the minimum quantity and minimum unit price in the sales_info
table using the MIN() function
SQL Code:
SELECT MIN(quantity) AS min_quantity, MIN(unit_price) AS
min_unit_price FROM sales_info;
Step 7: Use the MAXIMUM function

7.1 Find the maximum quantity and maximum unit price in the sales_info
table using the MAX() function
SQL Code:
SELECT MAX(quantity) AS max_quantity, MAX(unit_price) AS
max_unit_price FROM sales_info;

Aggregate functions are executed on a specific dataset using these procedures.

You might also like