mysql

Khóa học miễn phí MySQL – ROLLUP nhận dự án làm có lương

Posted on by user

MySQL – ROLLUP

Table of content

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

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);

To calculate grand total of a column through a JavaScript program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the query() function of mysql2 library as follows −

sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
con.query(sql);

To calculate grand total of a column through a Java program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the JDBC function executeQuery() as follows −

String sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
statement.executeQuery(sql);

To calculate grand total of a column through a Python program use ROLLUP with aggregate function, we need to execute the “SELECT” statement using the execute() function of the MySQL Connector/Python as follows −

rollup_query = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"
cursorObj.execute(rollup_query)

Example

Following are the programs −

$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:       


var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

// Connecting to MySQL
con.connect(function (err) {
    if (err) throw err;
    console.log("Connected!");
    console.log("--------------------------");

    // Create a new database
    sql = "Create Database TUTORIALS";
    con.query(sql);

    sql = "USE TUTORIALS";
    con.query(sql);

    sql = "CREATE TABLE PRODUCT (PRODUCT_ID INT,PRODUCT_NAME VARCHAR(50),PRODUCT_COUNT INT,MONTH VARCHAR(20));"
    con.query(sql);

    sql = "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'');"
    con.query(sql);

    sql = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
    con.query(sql, function(err, result){
      if (err) throw err
      console.log(result);
    });
});

Output

The output obtained is as shown below −

Connected!
--------------------------
[
  { ''SUM(PRODUCT_COUNT)'': ''14680'', MONTH: ''February'' },
  { ''SUM(PRODUCT_COUNT)'': ''3711'', MONTH: ''January'' },
  { ''SUM(PRODUCT_COUNT)'': ''18391'', MONTH: null }
]   

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class RollUp {
    public static void main(String[] args) {
        String url = "jdbc:mysql://localhost:3306/TUTORIALS";
        String user = "root";
        String password = "password";
        ResultSet rs;
        try {
            Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            //create a table with name Product
            String sql = "CREATE TABLE PRODUCT ( PRODUCT_ID INT, PRODUCT_NAME VARCHAR(50), PRODUCT_COUNT INT, MONTH VARCHAR(20) )";
            st.execute(sql);
            System.out.println("Product table created successfully....!");
            //let''s insert some records into it...
            String sql1 = "INSERT INTO PRODUCT VALUES(101, ''Comb'', 2345, ''January''),  (102, ''Coffee Mugs'', 1242, ''January''), (103, ''Cutlery'', 124, ''January''), (101, ''Comb'', 3263, ''February'')";
            st.execute(sql1);
            System.out.println("Records inserted successfully...!");
            //print table records
            String sql2 = "SELECT * FROM PRODUCT";
            rs = st.executeQuery(sql2);
            System.out.println("Table records: ");
            while(rs.next()) {
                String PRODUCT_ID = rs.getString("PRODUCT_ID");
                String PRODUCT_NAME = rs.getString("PRODUCT_NAME");
                String PRODUCT_COUNT = rs.getString("PRODUCT_COUNT");
                String MONTH = rs.getString("MONTH");
                System.out.println("PRODUCT_ID: " + PRODUCT_ID + ", PRODUCT_NAME: " + PRODUCT_NAME + ", PRODUCT_COUNT: " + PRODUCT_COUNT + ", MONTH: " + MONTH);
            }
            //let''s calculate the sum of product with RollUp
            String sql3 = "SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP";
            rs = st.executeQuery(sql3);
            System.out.println("Sum of product: ");
            while(rs.next()) {
                String sum = rs.getString("SUM(PRODUCT_COUNT)");
                String MONTH = rs.getString("MONTH");
                System.out.println("Sum: " + sum + ", MONTH: " + MONTH);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }
    }
}

Output

The output obtained is as shown below −

Product table created successfully....!
Records 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: 3263, MONTH: February
Sum: 3711, MONTH: January
Sum: 6974, MONTH: null

import mysql.connector
# Establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
# Creating a cursor object
cursorObj = connection.cursor()
# providing rollup query
rollup_query = """SELECT SUM(PRODUCT_COUNT), MONTH FROM PRODUCT GROUP BY MONTH WITH ROLLUP"""
cursorObj.execute(rollup_query)
# Fetching and printing the results
results = cursorObj.fetchall()
print("Rollup Results:")
for row in results:
    print(f"Product Count: {row[0]}, MONTH: {row[1]}")
# Closing the cursor and connection
cursorObj.close()
connection.close()

Output

The output obtained is as shown below −

Rollup Results:
Product Count: 14680, MONTH: February
Product Count: 3711, MONTH: January
Product Count: 18391, MONTH: None

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user