If you need to show values with
totals and subtotals in a report, you could make two separate queries.
But it is safer and quicker to do both in one go.
You
might need to produce a report that shows values and calculates subtotals. In
Figure, the subtotals are listed at the end of each group.
Table values and subtotals
| item | serialNumber | price |
|---|
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | subtotal | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | subtotal | 30 |
| Cowl | 4 | 10 |
| Cowl | subtotal | 10 |
For a simple query like this, the time cost
of the round trip to the database server may dominate the cost of the
query itself. So, making two trips to the database is a lot like going
to the bar to buy a drink, walking away, and then going back to the bar
for your change.
More than one trip to the database also
introduces the possibility that someone else has added a row between
your visits. If that happens, your total could be wrong.
A simple
UNION will add the subtotals to your result:
mysql> SELECT item, serialNumber, price FROM source
-> UNION
-> SELECT item, NULL, SUM(price)
-> FROM source
-> GROUP BY item
-> ORDER BY item;
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | NULL | 20 |
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Bowl | NULL | 30 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Cowl | NULL | 10 |
| Cowl | 4 | 10 |
+------+--------------+-------+
The database server still has roughly the
same amount of work to do as before, but the overall performance should
be improved because you have saved the cost of sending a query and
getting the results back.
The query works fine. The only slight
problem is that subtotals show up as the first row for each item
(depending on the SQL implementation you are using). Traditionally, the
subtotals show up as the last item in each run.
You can change the ORDER
BY expression to c,
COALESCE(seq,
1E9). This will ensure that the NULL values in the serialNumber column show up last (1E9 is the number 1,000,000,000). You can have the UNION as a derived table to make it clear that the ORDER
BY applies to the whole UNION:
mysql> SELECT item, serialNumber, price FROM(
-> SELECT item, serialNumber, price FROM source
-> UNION
-> SELECT item, NULL, SUM(price)
-> FROM source
-> GROUP BY item
-> ) t
-> ORDER BY item, COALESCE(serialNumber,1E9);
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
+------+--------------+-------+
ROLLUP and GROUPING SETS
SQL Server, MySQL, and DB2 can do this using the
WITH
ROLLUP clause.
Oracle has a
GROUPING
SETS clause that covers this (
DB2 supports this as well).
SQL Server, MySQL, and DB2
In SQL Server, MySQL, and DB2:
mysql> SELECT item, serialNumber, SUM(price)
-> FROM source
-> GROUP BY item,serialNumber WITH ROLLUP;
+------+--------------+------------+
| item | serialNumber | SUM(price) |
+------+--------------+------------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
| NULL | NULL | 60 |
+------+--------------+------------+
Oracle and DB2
In Oracle and DB2:
SQL> SELECT item, serialNumber, SUM(price)
2 FROM source
3 GROUP BY GROUPING SETS ((item,serialNumber),(item),( ));
ITEM SERIALNUMBER SUM(PRICE)
------------------------------ ------------ ----------
Awl 1 10
Awl 3 10
Awl 20
Bowl 2 10
Bowl 5 10
Bowl 6 10
Bowl 30
Cowl 4 10
Cowl 10
60
Hacking the Hack
Of course, there is no reason why you shouldn't include the
GRAND
TOTAL in another
UNION:
mysql> SELECT item, serialNumber, price FROM(
-> SELECT item, serialNumber, price FROM source
-> UNION
-> SELECT item, NULL, SUM(price)
-> FROM source
-> GROUP BY item
-> UNION
-> SELECT NULL, NULL, SUM(price)
-> FROM source
-> ) t
-> ORDER BY COALESCE(item,'zzz'), COALESCE(serialNumber,1E9);
+------+--------------+-------+
| item | serialNumber | price |
+------+--------------+-------+
| Awl | 1 | 10 |
| Awl | 3 | 10 |
| Awl | NULL | 20 |
| Bowl | 2 | 10 |
| Bowl | 5 | 10 |
| Bowl | 6 | 10 |
| Bowl | NULL | 30 |
| Cowl | 4 | 10 |
| Cowl | NULL | 10 |
| NULL | NULL | 60 |
+------+--------------+-------+
But having totals showing in the same
column as the values themselves can make for a confusing report. If you
can format the subtotals and totals so that they stand out, it can help.
But it improves readability if you can put these in different columns.
You can add two more columns to the UNION. It takes a little care to put the NULL values in the right place:
mysql> SELECT item,serialNumber,price,sub,grand FROM (
-> SELECT item,serialNumber,price,NULL AS sub,NULL AS grand
-> FROM source
-> UNION
-> SELECT item,NULL, NULL, SUM(price), NULL
-> FROM source
-> GROUP BY item
-> UNION
-> SELECT NULL,NULL, NULL, NULL, SUM(price)
-> FROM source
-> ) t
-> ORDER BY COALESCE(item,'zzz'), COALESCE(serialNumber,1E9);
+------+--------------+-------+------+-------+
| item | serialNumber | price | sub | grand |
+------+--------------+-------+------+-------+
| Awl | 1 | 10 | NULL | NULL |
| Awl | 3 | 10 | NULL | NULL |
| Awl | NULL | NULL | 20 | NULL |
| Bowl | 2 | 10 | NULL | NULL |
| Bowl | 5 | 10 | NULL | NULL |
| Bowl | 6 | 10 | NULL | NULL |
| Bowl | NULL | NULL | 30 | NULL |
| Cowl | 4 | 10 | NULL | NULL |
| Cowl | NULL | NULL | 10 | NULL |
| NULL | NULL | NULL | NULL | 60 |
+------+--------------+-------+------+-------+
Source : Direct Link
*If this site helps you, please click the link below to say Thanks*
Please Register here to Say Thank You :)
