Get total in the last row of MySQL result?

MySQLMySQLi Database

To get total in the last row of MySQL result, use the following syntax −

(
   SELECT yourColumnName1,
   yourColumnName2,
   yourColumnName3,
   .
   .
   N
   FROM yourTableName
)
UNION
(
   SELECT "yourMessage" AS anyAliasName1,
   SUM(yourColumnName1) AS anyAliasName2,
   SUM(yourColumnName2) AS anyAliasName3,
   .
   .
   N
   FROM yourTableName
);

To understand the above syntax, let us create a table. The query to create a table is as follows −

mysql> create table ProductDemo
   -> (
   -> ProductId varchar(10),
   -> ProductQuantity int,
   -> ProductValue int
   -> );
Query OK, 0 rows affected (0.63 sec)

Insert some records in the table using insert command. The query is as follows −

mysql> insert into ProductDemo values('Product-1',10,300);
Query OK, 1 row affected (0.10 sec)
mysql> insert into ProductDemo values('Product-2',5,200);
Query OK, 1 row affected (0.17 sec)
mysql> insert into ProductDemo values('Product-3',7,340);
Query OK, 1 row affected (0.13 sec)
mysql> insert into ProductDemo values('Product-4',20,500);
Query OK, 1 row affected (0.10 sec)
mysql> insert into ProductDemo values('Product-5',30,1000);
Query OK, 1 row affected (0.42 sec)

Display all records from the table using a select statement. The query is as follows −

mysql> select *from ProductDemo;

The following is the output −

+-----------+-----------------+--------------+
| ProductId | ProductQuantity | ProductValue |
+-----------+-----------------+--------------+
| Product-1 |              10 |          300 |
| Product-2 |               5 |          200 |
| Product-3 |               7 |          340 |
| Product-4 |              20 |          500 |
| Product-5 |              30 |         1000 |
+-----------+-----------------+--------------+
5 rows in set (0.00 sec)

Here is the query to get total in the last row of MySQL result −

mysql> (SELECT ProductId,
   -> ProductQuantity,
   -> ProductValue
   -> FROM ProductDemo)
   -> UNION
   -> (SELECT "Total" AS ProductName,
   -> SUM(ProductQuantity) AS TotalQuantity,
   -> SUM(ProductValue) AS TotalValue   
   -> FROM ProductDemo);

Output

+-----------+-----------------+--------------+
| ProductId | ProductQuantity | ProductValue |
+-----------+-----------------+--------------+
| Product-1 |              10 |          300 |
| Product-2 |               5 |          200 |
| Product-3 |               7 |          340 |
| Product-4 |              20 |          500 |
| Product-5 |              30 |         1000 |
| Total     |              72 |         2340 |
+-----------+-----------------+--------------+
6 rows in set (0.00 sec)
raja
Published on 19-Mar-2019 13:22:55
Advertisements