Ultimate Guide to Count Subtotal and Grand Total in Mysql

When you use the SUM or COUNT statement to calculate the value of rows, you may want to calculate the subtotal and grand total too, but sometimes, it’s not easy task, so today we’ll discuss how to count subtotal and grand total in MySQL

Table of contents:

Before going any further discussing the three ways above, you can download a .sql file used in this article. This file will create a sales table that contains approximately 400 sales data with the layout looks like the following table:

sales_id product_id payment_date payment_year payment_amount customer_id
1 100 2016-09-20 2016 265 1
2 100 2016-10-11 2016 270 2
3 101 2016-08-17 2016 250 2
4 102 2016-02-08 2016 255 1
5 100 2016-06-05 2016 290 3
Download SQL File

I. How to Count subtotal and grand total in MySQL using PHP

Probably this is the common and easiest way to do the task, as it more practical, flexible, and easy to do. We don’t need an extra logic to the MySQL Query, the script looks like the following:

$sql = 'SELECT sales_id, product_id, SUM(payment_amount) AS total 
		FROM `sales` 
		GROUP BY customer_id, product_id';
$stmt = $pdo->prepare($sql);
$stmt->execute();

echo '<table>
		<thead>
			<tr>
				<td>CUSTOMER ID</td>
				<td>PRODUCT ID</td>
				<td>TOTAL</td>
			</tr>
		</thead>
		<tbody>';
$total = 0;
while ($row = $stmt->fetch())
{
	echo '<tr>
			<td>' . $row['customer_id'] . '</td>
			<td>' . $row['product_id'] . '</td>
			<td>' . number_format($row['total']) . '</td>
		</tr>';
	$total += $row['total'];
}
echo '<tr>
		<td colspan="2">TOTAL</td>
		<td>' . number_format($total) . '</td>
	 </tr>
	</tbody>
</table>';

The result is:

CUSTOMER ID PRODUCT ID TOTAL
1 100 28,885
1 101 38,225
2 100 25,855
2 101 41,295
TOTAL 134,260

Although it easy to do, there are several drawbacks we need to consider:

  • When the query results used in other programming languages, we have to rewrite (porting) the code to the language style.
  • In certain situations, such as: when we need to count subtotal, then our code become more complicated depending on the complexity of the layout that we want to display. For example, we want to get the following output:
PAYMENT YEAR CUSTOMER ID PRODUCT ID TOTAL
2015 1 100 13,660
2015 1 101 19,885
SUB TOTAL 33,545
2015 2 100 15,145
2015 2 101 19,595
SUB TOTAL 34,740
SUB TOTAL 2015 68,285
2016 1 100 15,225
2016 1 101 18,340
SUB TOTAL 33,565
2016 2 100 10,710
2016 2 101 21,700
SUB TOTAL 32,410
SUB TOTAL 2016 65,975
GRAND TOTAL 134,260

We need to change our PHP script into:

// Change the query result into associative array and save to $result variable
$result = $stmt->fetchAll(PDO::FETCH_ASSOC);

echo '<table>
		<thead>
			<tr>
				<th>PAYMNT YEAR</th>
				<th>CUSTOMER ID</th>
				<th>PRODUCT ID</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
		
$subtotal_cust = $subtotal_year = $grand_total = 0;
foreach ($result as $key => $row)
{
	$subtotal_cust += $row['payment_amount'];
	$subtotal_year += $row['payment_amount'];
	echo '<tr>
			<td>'.$row['payment_year'].'</td>
			<td>'.$row['customer_id'].'</td>
			<td>'.$row['product_id'].'</td>
			<td class="right">'.number_format($row['payment_amount']).'</td>
		</tr>';
	
	// SUB TOTAL by customer_id
	if (@$result[$key+1]['customer_id'] != $row['customer_id']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL</td>
			<td></td>
			<td class="right">'.number_format($subtotal_cust).'</td>
		</tr>';
		$subtotal_cust = 0;
	}
	
	// SUB TOTAL by payment_year
	if (@$result[$key+1]['payment_year'] != $row['payment_year']) {
		echo '<tr class="subtotal">
			<td></td>
			<td>SUB TOTAL ' . $row['payment_year'] . '</td>
			<td></td>
			<td class="right">'.number_format($subtotal_year).'</td>
		</tr>';
		$subtotal_year = 0;
	} 
	$grand_total += $row['payment_amount'];
}

// GRAND TOTAL
echo '<tr class="total">
		<td></td>
		<td>GRAND TOTAL</td>
		<td></td>
		<td class="right"> ' . number_format($grand_total) . '</td>
	</tr>
	</tbody>
</table>
</body>
</html>';

Notice the 2nd line, we save all query results into $result variable: $result = $stmt->fetchAll(PDO::FETCH_ASSOC).

We should do this because to create subtotals we need to know the value of the next row of the query result (line 28).

If the query results just a little row of table, then it’s OK in dealing with computer’s memory (RAM) but if the query results lots of row or maybe tons of rows, then for sure it will overload the memory with just a single variable ( $result ) that will slow down the application

Some Limitations

Consider the above shortcomings, I personally think that we should use this as the last alternative way when there is no other way to do the task, for example on the complex pivot table that involving many aggregation functions.

II. Count Subtotal and Grand Total in MySQl Using WITH ROLLUP

In MySQL, WITH ROLLUP statement is used together with the GROUP BY statement, this can be used both to calculate the subtotal or grand total according to the column we used in the GROUP BY statement.

The advantage of this statement is it simple and can produce a total and subtotal in just in one query, that makes our work much easier and simpler.

For example, to calculate the subtotal and grand total of payment_amount field (as the previous example), we only need to add WITH ROLLUP statement at the end of the query, so the query become like this:

SELECT payment_year, customer_id, product_id, SUM(payment_amount) AS total 
FROM `sales` 
GROUP BY payment_year, customer_id, product_id WITH ROLLUP

The result is:

SQL Result Using With Rollup Statement

 

Pay attention to the NULL value, the WITH ROLLUP statement perform three functions summation based on the columns we used in the GROUP BY statement as follows:

How to Count Subtotal and Grand Total In MySQL - Using With Rollup

Explanation:

  1. payment_year. MySQL adds a new row to calculate the payment_amount value based on the payment_year field, by providing NULL value in customer_id column.
  2. payment_year and customer_id. MySQL adds a new row to calculate payment_amount value based on the payment_year and customer_id field, by providing NULL values in the product_id column.
  3. payment_year, customer_id and product_id. MySQL adds a new row to calculate payment_amount for all product_id, customer_id and payment_year column by providing NULL values in all those columns

Notice the red box, why the value look like that? is there a mistake? no there is no mistake, the value is freely chosen by MySQL, but usually same as the value in the previous row.

1 Changing NULL Value to SUB TOTAL Text

Furthermore, the NULL value are meaningless, so that we need make a change.

We can change the NULL value to other value using the COALESCE function, but some of the others use the IFNULL function, personally I prefer to use COALESCE function as it included in the ANSI SQL standard (92), making it compatible with SQL language in other databases, such as MSSQL.

Note: in MSSQL we must replace the IFNULL with ISNULL.

Using COALESCE function, we change the above SQL command into this:

SELECT COALESCE(payment_year, 'TOTAL') AS payment_year, 
		COALESCE(customer_id, 'SUB TOTAL') AS customer_id,
		COALESCE(product_id, 'SUB TOTAL') AS product_id,
		SUM(payment_amount) AS payment_amount 
FROM `sales` 
GROUP BY payment_year, customer_id, product_id WITH ROLLUP

The Result:

+--------------+-------------+------------+----------------+
| payment_year | customer_id | product_id | payment_amount |
+--------------+-------------+------------+----------------+
| 2015         | 1           | 100        |          13660 |
| 2015         | 1           | 101        |          19885 |
| 2015         | 1           | SUB TOTAL  |          33545 |
| 2015         | 2           | 100        |          15145 |
| 2015         | 2           | 101        |          19595 |
| 2015         | 2           | SUB TOTAL  |          34740 |
| 2015         | SUB TOTAL   | SUB TOTAL  |          68285 |
| 2016         | 1           | 100        |          15225 |
| 2016         | 1           | 101        |          18340 |
| 2016         | 1           | SUB TOTAL  |          33565 |
| 2016         | 2           | 100        |          10710 |
| 2016         | 2           | 101        |          21700 |
| 2016         | 2           | SUB TOTAL  |          32410 |
| 2016         | SUB TOTAL   | SUB TOTAL  |          65975 |
| TOTAL        | SUB TOTAL   | SUB TOTAL  |         134260 |
+--------------+-------------+------------+----------------+

If we want to display that table using PHP, the following script will do the task:

echo '<table class="grey">
		<thead>
			<tr>
				<th>YEAR</th>
				<th>CUSTOMER ID</th>
				<th>PRODUCT ID</th>
				<th>TOTAL</th>
			</tr>
		</thead>
		<tbody>';
while ($row = $stmt->fetch())
{
	$class = '';
	
	// if subtotal or grand total
	if ($row['payment_year'] == 'TOTAL') {
		$class = ' class="total"';
	} else if ($row['customer_id'] == 'SUB TOTAL' || $row['product_id'] == 'SUB TOTAL') {
		$class = ' class="subtotal"';
	}
	
	echo 
	'<tr'.$class.'>
		<td>' . $row['payment_year'] . '</td>
		<td>' . $row['customer_id'] . '</td>
		<td>' . $row['product_id'] . '</td>
		<td class="right">' . number_format($row['payment_amount']) . '</td>
	</tr>';
}
echo '
	</tbody>
</table>
</body>
</html>';

2 How to combine WITH ROLLUP and ORDER BY statement

To sort query results that contain WITH ROLLUP clause, we can not directly use the ORDER BY clause, because both clauses are independent (stand alone), use implicit order or explicit order instead.

Using Implicit Order

When we run the GROUP BY clause, at the same time, MySQL will also sort the data, but, because it is not stated clearly, it is often called “implicit order”.

The previous example shows that the data sorted by payment_year column ascendingly (from the smallest to the largest value), it also happen on the customer_id and product_id columns.

In an implicit order, we can change the sorting behavior by adding ASC or DESC option to the GROUP BY clause, for example, in the previous example we reverse the order of the data into descending order based on payment_year, customer_id, and product_id column.

SELECT  COALESCE(payment_year, 'TOTAL') as payment_year,
	COALESCE(customer_id, 'SUB TOTAL') AS customer_id,
	COALESCE(product_id, 'SUB TOTAL') AS product_id,
	SUM(payment_amount) AS payment_amount
FROM sales
GROUP BY payment_year DESC, customer_id DESC, product_id DESC
WITH ROLLUP

The result is:

+--------------+-------------+------------+----------------+
| payment_year | customer_id | product_id | payment_amount |
+--------------+-------------+------------+----------------+
| 2016         | 2           | 101        |          21700 |
| 2016         | 2           | 100        |          10710 |
| 2016         | 2           | SUB TOTAL  |          32410 |
| 2016         | 1           | 101        |          18340 |
| 2016         | 1           | 100        |          15225 |
| 2016         | 1           | SUB TOTAL  |          33565 |
| 2016         | SUB TOTAL   | SUB TOTAL  |          65975 |
| 2015         | 2           | 101        |          19595 |
| 2015         | 2           | 100        |          15145 |
| 2015         | 2           | SUB TOTAL  |          34740 |
| 2015         | 1           | 101        |          19885 |
| 2015         | 1           | 100        |          13660 |
| 2015         | 1           | SUB TOTAL  |          33545 |
| 2015         | SUB TOTAL   | SUB TOTAL  |          68285 |
| TOTAL        | SUB TOTAL   | SUB TOTAL  |         134260 |
+--------------+-------------+------------+----------------+

The table above shows that the payment_year, customer_id, and product_id are sorted in descending order. This is the easiest way to sort data that contain WITH ROLLUP clause.

However, in MySQL version 5.7, this feature has been deprecated, meaning that it is not recommended to be used, because, on the further version,  this feature will be removed.

Because of that reason, it is recommended to use an explicit order by using the ORDER BY clause.

Using Explicit Order

To be able to implement the explicit order, first, we have to create a temporary table (by using subquery) that contain the WITH ROLLUP clausa, then sort that table using the ORDER BY clause located on the main query.

Continuing the previous example, now we sort the year_payment column in a descending order:

SELECT payment_year, customer_id, product_id, payment_amount
FROM
(
	SELECT  COALESCE(payment_year, 0) as payment_year,
		COALESCE(customer_id, 'SUB TOTAL') AS customer_id,
		COALESCE(product_id, 'SUB TOTAL') AS product_id,
		SUM(payment_amount) AS payment_amount
    FROM sales
    GROUP BY payment_year, customer_id, product_id WITH ROLLUP
) as sales
ORDER BY payment_year DESC, customer_id, payment_year

The result:

+--------------+-------------+------------+----------------+
| payment_year | customer_id | product_id | payment_amount |
+--------------+-------------+------------+----------------+
|         2016 | 1           | 100        |          15225 |
|         2016 | 1           | 101        |          18340 |
|         2016 | 1           | SUB TOTAL  |          33565 |
|         2016 | 2           | 100        |          10710 |
|         2016 | 2           | 101        |          21700 |
|         2016 | 2           | SUB TOTAL  |          32410 |
|         2016 | SUB TOTAL   | SUB TOTAL  |          65975 |
|         2015 | 1           | SUB TOTAL  |          33545 |
|         2015 | 1           | 100        |          13660 |
|         2015 | 1           | 101        |          19885 |
|         2015 | 2           | 100        |          15145 |
|         2015 | 2           | 101        |          19595 |
|         2015 | 2           | SUB TOTAL  |          34740 |
|         2015 | SUB TOTAL   | SUB TOTAL  |          68285 |
|            0 | SUB TOTAL   | SUB TOTAL  |         134260 |
+--------------+-------------+------------+----------------+

Notice that on the 4th line, we use the statement: COALESCE(payment_year, 0) as payment_year NOT COALESCE (payment_year, 'TOTAL') as payment_year.

We use 0 to reflect the grand total because we sort year_payment column in descending order, so 0 will be placed on the last row, if we use COALESCE(thn_byr, 'TOTAL'), then the line will be placed in the first row.

3 Using limit statement within the WITH ROLLUP statement

The usage of limit statement within the with rollup sometimes give a result that does not meet expectations, due to the additional new lines generated by the with rollup (SUBTOTAL) will be included in the calculation of the limit, for example:

SELECT payment_year, customer_id, COALESCE(product_id, 'SUB TOTAL') as product_id, SUM(payment_amount) AS payment_amount 
FROM `sales` 
GROUP BY payment_year, customer_id, product_id WITH ROLLUP
LIMIT 5

The Result:

+--------------+-------------+------------+----------------+
| payment_year | customer_id | product_id | payment_amount |
+--------------+-------------+------------+----------------+
|         2015 |           1 | 100        |          13660 |
|         2015 |           1 | 101        |          19885 |
|         2015 |           1 | SUB TOTAL  |          33545 |
|         2015 |           2 | 100        |          15145 |
|         2015 |           2 | 101        |          19595 |
+--------------+-------------+------------+----------------+

III. Using Additional Query to Count Subtotal and Grand Total in MySQL (The Easiest Way)

So far using with rollup statement is sufficient to calculate the subtotal and grand total, but it has some limitations such as can only perform operations of addition, these problems can be overcome by this method.

In this method, we’ll add a new row using UNION statement to calculate the subtotal and grand total. This addition can be directly included in the main query or subquery, depending on the situation.

To calculate the grand total, we simply add a query in the main query, for example:

SELECT payment_year, customer_id, product_id, payment_amount
FROM
(
	SELECT payment_year, customer_id, COALESCE(product_id, 'SUB TOTAL') as product_id, SUM(payment_amount) AS payment_amount 
	FROM sales
	GROUP BY payment_year, customer_id, product_id
	ORDER BY payment_year DESC, payment_amount DESC
) as sales
UNION
SELECT 'GRAND TOTAL', null, null, SUM(payment_amount) AS payment_amount 
FROM sales

The output:

+--------------+-------------+------------+----------------+
| payment_year | customer_id | product_id | payment_amount |
+--------------+-------------+------------+----------------+
| 2016         |           2 | 101        |          21700 |
| 2016         |           1 | 101        |          18340 |
| 2016         |           1 | 100        |          15225 |
| 2016         |           2 | 100        |          10710 |
| 2015         |           1 | 101        |          19885 |
| 2015         |           2 | 101        |          19595 |
| 2015         |           2 | 100        |          15145 |
| 2015         |           1 | 100        |          13660 |
| GRAND TOTAL  |        NULL | NULL       |         134260 |
+--------------+-------------+------------+----------------+

Note that in this method, we can perform data processing more easily, in the above example we can sort payment and years field descending simultaneously, where it is quite difficult to do using the previous method.

1 Add SUB TOTAL and Add Various Aggregation

The use of manual query allows us to perform various aggregation functions.

The following example slightly modify the previous example, this time, we will:

  1. Sort the payment_year field descending, customer_id field ascending, and payment_amount field descending, then grouped by customer_id
  2. add a column that contains a percentage ratio of the value of payments compared to the total number of payment

The SQL:

SELECT SUM(payment_amount) FROM sales INTO @total;

SELECT payment_year, customer_id, product_id, SUM(payment_amount) as payment_amount, ROUND(SUM(payment_amount)/@total*100, 2) as ratio
FROM sales
GROUP BY payment_year, customer_id, product_id
	UNION
SELECT payment_year, CONCAT(customer_id, '-SUB TOTAL'), product_id, SUM(payment_amount) as payment_amount, ROUND(SUM(payment_amount)/@total*100, 2) as ratio
FROM sales
GROUP BY payment_year, customer_id
	UNION
SELECT payment_year, CONCAT('TOTAL ', payment_year), product_id, SUM(payment_amount) as payment_amount, ROUND(SUM(payment_amount)/@total*100, 2) as ratio
FROM sales
GROUP BY payment_year
	UNION
SELECT null, 'GRAND TOTAL', null, ROUND(@total), '100.00'
ORDER BY payment_year DESC, customer_id, payment_amount DESC

The result is:

+--------------+-------------+------------+----------------+--------+
| payment_year | customer_id | product_id | payment_amount | ratio  |
+--------------+-------------+------------+----------------+--------+
|         2016 | 1           |        101 |          18340 | 13.66  |
|         2016 | 1           |        100 |          15225 | 11.34  |
|         2016 | 1-SUB TOTAL |        100 |          33565 | 25.00  |
|         2016 | 2           |        101 |          21700 | 16.16  |
|         2016 | 2           |        100 |          10710 | 7.98   |
|         2016 | 2-SUB TOTAL |        100 |          32410 | 24.14  |
|         2016 | TOTAL 2016  |        100 |          65975 | 49.14  |
|         2015 | 1           |        101 |          19885 | 14.81  |
|         2015 | 1           |        100 |          13660 | 10.17  |
|         2015 | 1-SUB TOTAL |        100 |          33545 | 24.99  |
|         2015 | 2           |        101 |          19595 | 14.59  |
|         2015 | 2           |        100 |          15145 | 11.28  |
|         2015 | 2-SUB TOTAL |        100 |          34740 | 25.88  |
|         2015 | TOTAL 2015  |        100 |          68285 | 50.86  |
|         NULL | GRAND TOTAL |       NULL |         134260 | 100.00 |
+--------------+-------------+------------+----------------+--------+

Note that we store the grand total value to @grand_total variable, we do this because this value is used more than once, so it is not efficient if we calculate the value many times.

In the above example, we use words 1-SUB TOTAL, 2-SUB TOTAL, TOTAL 2016 TOTAL 2015 and GRAND TOTAL in the customer_id field.

We do this because we have to choose some words by some means when it sorted, the output will meet our expectation, for example in 2016, 1-SUB TOTAL is greater than 1, so it will be placed under 1, as well as 2 and TOTAL 2016.

2 Method #3-1: Solving The Order By – Adding New Column

Generating layout like the previous section completely depends on the choice of words to be sorted, the wrong choice will generate different sequences.

For example on the previous SQL command – line 7 – if we change the command: CONCAT(customer_id,'-SUB TOTAL') into CONCAT('SUB TOTAL-', customer_id), the result becomes:

+--------------+-------------+------------+----------------+--------+
| payment_year | customer_id | product_id | payment_amount | ratio  |
+--------------+-------------+------------+----------------+--------+
|         2016 | 1           |        101 |          18340 | 13.66  |
|         2016 | 1           |        100 |          15225 | 11.34  |
|         2016 | 2           |        101 |          21700 | 16.16  |
|         2016 | 2           |        100 |          10710 | 7.98   |
|         2016 | SUB TOTAL-1 |        100 |          33565 | 25.00  |
|         2016 | SUB TOTAL-2 |        100 |          32410 | 24.14  |
|         2016 | TOTAL 2016  |        100 |          65975 | 49.14  |
|         2015 | 1           |        101 |          19885 | 14.81  |
|         2015 | 1           |        100 |          13660 | 10.17  |
|         2015 | 2           |        101 |          19595 | 14.59  |
|         2015 | 2           |        100 |          15145 | 11.28  |
|         2015 | SUB TOTAL-1 |        100 |          33545 | 24.99  |
|         2015 | SUB TOTAL-2 |        100 |          34740 | 25.88  |
|         2015 | TOTAL 2015  |        100 |          68285 | 50.86  |
|         NULL | GRAND TOTAL |       NULL |         134260 | 100.00 |
+--------------+-------------+------------+----------------+--------+

The table above shows that the order is changed, not as expected, right?. How to solve it?

One way is to add new columns: sub_total and grand_total, this is possible if:

  • We do not use group by and
  • Thre is a field to identify the sequence of the rows, for example, an id field.

3 Making Temporary Index

Things become difficult if there is no sequence of rows, the queries require a fairly complex logic, for example, in the example above, we’ll create a temporary field contains the index (order) of the row.

To solve this, we need to create a temporary field containing an index (order) of the row, following the previous example:

  1. We’ll first create a field named idx that contains the sequence of the rows.
  2. Then, we use that value to create a query again so we get the payment_year‘s value of the next row and then we save it to a new column named idx_next_year

The SQL command is:

SET @idx=0, @idx2=0;

SELECT  SUM(payment_amount) FROM sales INTO @total;
SELECT  payment_year, customer_id, product_id, payment_amount, ratio
	,@idx:=@idx+1 as idx
	, (      
		SELECT CONCAT(rnum, '-', payment_year)
		FROM
		(
			SELECT payment_year, @idx2:=@idx2+1 as rnum
			 FROM 
			 (
				 SELECT  payment_year FROM sales GROUP BY payment_year DESC, customer_id, product_id
			 ) as tmp
		) as tmp
		WHERE rnum > @idx AND rnum < @idx+2   
	) AS idx_next_year
FROM (
    SELECT payment_year, customer_id, product_id
		,SUM(payment_amount) as payment_amount
		,ROUND(SUM(payment_amount)/@total*100, 2) as ratio
    FROM sales
    GROUP BY payment_year, customer_id, product_id
    ORDER BY payment_year DESC, customer_id
) new_sales

The Result:

+--------------+-------------+------------+----------------+-------+------+---------------+
| payment_year | customer_id | product_id | payment_amount | ratio | idx  | idx_next_year |
+--------------+-------------+------------+----------------+-------+------+---------------+
|         2016 |           1 |        100 |          15225 | 11.34 |    1 | 2-2016        |
|         2016 |           1 |        101 |          18340 | 13.66 |    2 | 3-2016        |
|         2016 |           2 |        100 |          10710 |  7.98 |    3 | 4-2016        |
|         2016 |           2 |        101 |          21700 | 16.16 |    4 | 5-2015        |
|         2015 |           1 |        100 |          13660 | 10.17 |    5 | 6-2015        |
|         2015 |           1 |        101 |          19885 | 14.81 |    6 | 7-2015        |
|         2015 |           2 |        100 |          15145 | 11.28 |    7 | 8-2015        |
|         2015 |           2 |        101 |          19595 | 14.59 |    8 | NULL          |
+--------------+-------------+------------+----------------+-------+------+---------------+

Note that we use @idx variable to store the sequence of the row and @idx2 to save the sequence the next row (@idx2 = @idx + 1).

Calculate The Subtotal and Grand Total Value

NEXT: we’ll test the value of the payment_year field. Once the value of the next row (idx_next_year field) differ from the current value, then we know that this is the last row of the current year, so we calculate the subtotal for the year.

Now we change the query into:

SET @idx=0, @idx2=0;

SELECT SUM(payment_amount) FROM sales INTO @total;
SELECT payment_year, customer_id, product_id, payment_amount, ratio
	,@idx:=@idx+1 as idx
	,CASE WHEN payment_year =
			(      
				SELECT payment_year
				FROM
				(
					SELECT payment_year, @idx2:=@idx2+1 as rnum
					 FROM 
					 (
						 SELECT  payment_year FROM sales GROUP BY payment_year DESC, customer_id, product_id
					 ) as tmp
				) as tmp
				WHERE rnum > @idx AND rnum < @idx+2   
			)
		THEN ''
		ELSE (SELECT SUM(payment_amount) FROM sales WHERE payment_year = new_sales.payment_year)
		END as sub_total
	,IF (@idx = (SELECT COUNT(payment_year) 
				 FROM (SELECT payment_year FROM sales GROUP BY payment_year, customer_id, product_id) as tmp
				)
	, (SELECT SUM(payment_amount) FROM sales)
	, '') as total
FROM (
    SELECT payment_year, customer_id, product_id
			,SUM(payment_amount) as payment_amount
			,ROUND(SUM(payment_amount)/@total*100, 2) as ratio
    FROM sales
    GROUP BY payment_year, customer_id, product_id
    ORDER BY payment_year DESC, customer_id
) new_sales

The result:

+--------------+-------------+------------+----------------+-------+------+-----------+--------+
| payment_year | customer_id | product_id | payment_amount | ratio | idx  | sub_total | total  |
+--------------+-------------+------------+----------------+-------+------+-----------+--------+
|         2016 |           1 |        100 |          15225 | 11.34 |    1 |           |        |
|         2016 |           1 |        101 |          18340 | 13.66 |    2 |           |        |
|         2016 |           2 |        100 |          10710 |  7.98 |    3 |           |        |
|         2016 |           2 |        101 |          21700 | 16.16 |    4 | 65975     |        |
|         2015 |           1 |        100 |          13660 | 10.17 |    5 |           |        |
|         2015 |           1 |        101 |          19885 | 14.81 |    6 |           |        |
|         2015 |           2 |        100 |          15145 | 11.28 |    7 |           |        |
|         2015 |           2 |        101 |          19595 | 14.59 |    8 | 68285     | 134260 |
+--------------+-------------+------------+----------------+-------+------+-----------+--------+

Notice the SQL commands on line 20, we calculate the subtotal for the same year when the value of the variable @idx and @idx2 are different. This query can inspire us to calculate the subtotal of customer_id.

This method is safer, at least, when we change the order of the year, the value in the sub_total field will be adjusted.

See the last SQL command, now change all the words DESC into ASC and re-run the command, you should see the value of the payment_year field will be sorted ascending and the value of other fields will follow, as well as the sub_total and grand_total field.

However, this method has a limitation, the query is complex, so the greater data to be processed, the longer time it takes.

Conclusion

From the above discussion, we can conclude that there are three alternative ways to count subtotal and grand total in MySQL:

  1. The calculation performed at the application level, such as PHP, this method tends to be easier to use because the programming logics supported by the language are wider than the logic in the SQL language.

    However, we should use this method as the last alternative as we have to rewrite (adjust) the code if we want to use it in an application that has a different programming language.

  2. Using with rollup statement. This method is more simple and easy, but it has some limitations including only support the addition operation.
  3. Using additional query. This method is more flexible than using with rollup statement because we can do many aggregation functions.

    Using this method will make the sql query more complex, that will potentialy reduce the performance of the database.

so, which one should we use? it is back to your conditions, I personally have a principle that the data processing -as much as possible- done on the single execution of SQL query, how about you?

Related Post

Leave a comment

Like Us

Social