Database Reference
In-Depth Information
| 2166 |
+-------------+
Then calculate the per-group values and use the overall total to compute the percentages:
mysql>
SELECT name,
->
SUM(miles) AS 'miles/driver',
->
(SUM(miles)*100)/@total AS 'percent of total miles'
->
FROM driver_log GROUP BY name;
+-------+--------------+------------------------+
| name | miles/driver | percent of total miles |
+-------+--------------+------------------------+
| Ben | 362 | 16.7128 |
| Henry | 911 | 42.0591 |
| Suzi | 893 | 41.2281 |
+-------+--------------+------------------------+
To combine the two statements into one, use a subquery that computes the total miles:
SELECT
name
,
SUM
(
miles
)
AS
'miles/driver'
,
(
SUM
(
miles
)
*
100
)
/
(
SELECT
SUM
(
miles
)
FROM
driver_log
)
AS
'percent of total miles'
FROM
driver_log
GROUP
BY
name
;
A similar problem uses multiple summary levels to compare per-group summary values
with the corresponding overall summary value. Suppose that you want to display drivers
who had a lower average miles per day than the group average. Calculate the overall
average in a subquery, and then compare each driver's average to the overall average
using a
HAVING
clause:
mysql>
SELECT name, AVG(miles) AS driver_avg FROM driver_log
->
GROUP BY name
->
HAVING driver_avg < (SELECT AVG(miles) FROM driver_log);
+-------+------------+
| name | driver_avg |
+-------+------------+
| Ben | 120.6667 |
| Henry | 182.2000 |
+-------+------------+
To display different summary-level values (and not perform calculations involving one
summary level against another), add
WITH
ROLLUP
to the
GROUP
BY
clause:
mysql>
SELECT name, SUM(miles) AS 'miles/driver'
->
FROM driver_log GROUP BY name WITH ROLLUP;
+-------+--------------+
| name | miles/driver |
+-------+--------------+
| Ben | 362 |
| Henry | 911 |
| Suzi | 893 |
| NULL | 2166 |
+-------+--------------+
