MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

ROLLUP
The MySQL ROLLUP Clause
The MySQL ROLLUP Clause is an extension of the GROUP BY Clause. It is used with aggregate functions in MySQL to find the grand total or a summary of a column's values (also known as super-aggregate of a column), in an extra row within a table.
Consider a manufacturing factory that tracks monthly production data in a table. To determine the annual product production, you can use the SUM() aggregate function along with ROLLUP. However, if you need to find out the number of months where production falls below a specific threshold, ROLLUP will allow you to count such months as well using the COUNT() function.
Syntax
Following is the syntax of ROLLUP clause in MySQL 
SELECT AggregateFunction(column_name(s)), column_name(s)
FROM table_name
GROUP BY column_name(s)
WITH ROLLUP;
Example
First, we will create a table named "PRODUCT" containing production information such as product ID, product name, product count, and manufacturing month within an organization 
CREATE TABLE PRODUCT (
   PRODUCT_ID INT,
   PRODUCT_NAME VARCHAR(50),
   PRODUCT_COUNT INT,
   MONTH VARCHAR(20)
);
Now, let us insert some data into the above-created table 
INSERT INTO PRODUCT VALUES
(101, 'Comb', 2345, 'January'),
(102, 'Coffee Mugs', 1242, 'January'),
(103, 'Cutlery', 124, 'January'),
(101, 'Comb', 3263, 'February'),
(102, 'Coffee Mugs', 10982, 'February'),
(103, 'Cutlery', 435, 'February');
The PRODUCT table obtained is as follows 
PRODUCT_ID
PRODUCT_NAME
PRODUCT_COUNT
MONTH
101
Comb
2345
January
102
Coffee Mugs
1242
January
103
Cutlery
124
January
101
Comb
3263
February
102
Coffee Mugs
10982
February
103
Cutlery
435
February
Now, let us to find the sum of products manufactured each MONTH using ROLLUP as shown below 
SELECT SUM(PRODUCT_COUNT), MONTH 
FROM PRODUCT 
GROUP BY MONTH WITH ROLLUP;
Output
you can observe in the output below that the individual product counts for both January and February are calculated, and the grand total of total production is displayed in the third row using ROLLUP 
SUM(PRODUCT_COUNT)
MONTH
14680
February
3711
January
18391
NULL
ROLLUP on Multiple Columns
You can also use ROLLUP on multiple columns by grouping them together using GROUP BY clause.
Example
Here, we are applying the GROUP BY clause on columns 'PRODUCT_ID' and 'PRODUCT_NAME' of the PRODUCT table 
SELECT PRODUCT_ID, 
COUNT(PRODUCT_ID) AS PROD_ID_COUNT, 
PRODUCT_NAME, 
COUNT(PRODUCT_NAME) AS PROD_ID_NAME 
FROM PRODUCT 
GROUP BY PRODUCT_ID, PRODUCT_NAME;
We get the following output 
PRODUCT_ID
PROD_ID_COUNT
PRODUCT_NAME
PROD_ID_NAME
101
2
Comb
2
102
2
Coffee Mugs
2
103
2
Cutlery
2
Now, calculate the summary of these two rows using ROLLUP as shown in the following query 
SELECT PRODUCT_ID, 
COUNT(PRODUCT_ID) AS PROD_ID_COUNT, 
PRODUCT_NAME, 
COUNT(PRODUCT_NAME) AS PROD_ID_NAME
FROM PRODUCT 
GROUP BY PRODUCT_ID, PRODUCT_NAME
WITH ROLLUP;
You can see in the output below that the summary is calculated not only at the final level but also at two levels. For every product name, a column summary is displayed 
PRODUCT_ID
PROD_ID_COUNT
PRODUCT_NAME
PROD_ID_NAME
101
2
Comb
2
101
2
NULL
2
102
2
Coffee Mugs
2
102
2
NULL
2
103
2
Cutlery
2
103
2
NULL
2
NULL
6
NULL
6
Rollup Using Client Program
We can also perform rollup Using Client Program.
Syntax
PHPNodeJSJavaPython
To calculate grand total of a column through a PHP program use ROLLUP with aggregate function, we need to execute the "SELECT" statement using the mysqli function query() as follows 
$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
$mysqli->query($sql);
Example
Following are the programs 
PHPNodeJSJavaPython
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'password';
$db = 'TUTORIALS';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $db);
if ($mysqli->connect_errno) {
    printf("Connect failed: %s", $mysqli->connect_error);
    exit();
}
//printf('Connected successfully.');
$sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
if($mysqli->query($sql)){
    printf("Product table created successfully....!");
}
//now let's insert some records into the table
$sql = "INSERT INTO PRODUCT VALUES(101, 'Comb', 2345, 'January')";
if($mysqli->query($sql)){
    printf("First record inserted successfully...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(102, 'Coffee Mugs', 1242, 'January')";
if($mysqli->query($sql)){
    printf("Second record inserted successfully...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(103, 'Cutlery', 124, 'January')";
if($mysqli->query($sql)){
    printf("Third record inserted successfully...!\n");
}
$sql = "INSERT INTO PRODUCT VALUES(101, 'Comb', 3263, 'February')";
if($mysqli->query($sql)){
    printf("Fourth record inserted successfully...!\n");
}
//display the table records
$sql = "SELECT * FROM PRODUCT";
if($result = $mysqli->query($sql)){
    printf("Table records: \n");
while($row = mysqli_fetch_array($result)){
    printf("PRODUCT_ID: %d, PRODUCT_NAME: %s, PRODUCT_COUNT: %d, MONTH: %s",
    $row['PRODUCT_ID'],
    $row['PRODUCT_NAME'],
    $row['PRODUCT_COUNT'],
    $row['MONTH']);
    printf("\n");
}}
//let's find the sum of product
$sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
if($result = $mysqli->query($sql)){
    printf("Sum of product: \n");
    while($row = mysqli_fetch_array($result)){
        printf("Sum of product: %d, MONTH: %s", $row['SUM(PRODUCT_COUNT)'], $row['MONTH']);
        printf("\n");
    }
}
if($mysqli->error){
    printf("Error message: ", $mysqli->error);
}
$mysqli->close();
Output
The output obtained is as shown below 
Product table created successfully....!
First record inserted successfully...!
Second record inserted successfully...!
Third record inserted successfully...!
Fourth record inserted successfully...!
Table records: 
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 2345, MONTH: January
PRODUCT_ID: 102, PRODUCT_NAME: Coffee Mugs, PRODUCT_COUNT: 1242, MONTH: January
PRODUCT_ID: 103, PRODUCT_NAME: Cutlery, PRODUCT_COUNT: 124, MONTH: January
PRODUCT_ID: 101, PRODUCT_NAME: Comb, PRODUCT_COUNT: 3263, MONTH: February
Sum of product:
Sum of product: 3263, MONTH: February
Sum of product: 3711, MONTH: January
Sum of product: 6974, MONTH:       
﻿
﻿
﻿
﻿
﻿

PRODUCT_ID	PRODUCT_NAME	PRODUCT_COUNT	MONTH
101	Comb	2345	January
102	Coffee Mugs	1242	January
103	Cutlery	124	January
101	Comb	3263	February
102	Coffee Mugs	10982	February
103	Cutlery	435	February

PRODUCT_ID	PROD_ID_COUNT	PRODUCT_NAME	PROD_ID_NAME
101	2	Comb	2
101	2	NULL	2
102	2	Coffee Mugs	2
102	2	NULL	2
103	2	Cutlery	2
103	2	NULL	2
NULL	6	NULL	6

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

ROLLUP

The MySQL ROLLUP Clause

Syntax

Example

Output

ROLLUP on Multiple Columns

Example

Rollup Using Client Program

Syntax

Example

Output