Monday 16 July 2018

Ultimate Guide to Count Subtotal and Grand Total in Mysql

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:
  1. Using scripts such as PHP to count subtotal and grand total
  2. Count subtotal and grand total in MySQL using WITH ROLLUP statement.
  3. Using Additional Query (UNION statement) to count subtotal and grand total – The Easiest Way
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_idproduct_idpayment_datepayment_yearpayment_amountcustomer_id
11002016-09-2020162651
21002016-10-1120162702
31012016-08-1720162502
41022016-02-0820162551
51002016-06-0520162903

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 IDPRODUCT IDTOTAL
110028,885
110138,225
210025,855
210141,295
TOTAL134,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 YEARCUSTOMER IDPRODUCT IDTOTAL
2015110013,660
2015110119,885
SUB TOTAL33,545
2015210015,145
2015210119,595
SUB TOTAL34,740
SUB TOTAL 201568,285
2016110015,225
2016110118,340
SUB TOTAL33,565
2016210010,710
2016210121,700
SUB TOTAL32,410
SUB TOTAL 201665,975
GRAND TOTAL134,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:


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_amountfor 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.

1Changing 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>';

2How 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 BYclause, 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.

3Using 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.

1Add 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.

2Method #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.

3Making 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?

0 comments:

Post a Comment

Subscribe to: Post Comments (Atom)