We can also make a case for doing math so that we can analyze our data from a business perspective. This could also be called business analytics. This would be part of the total make up of your business analytics and business making decisions.
For example, we'd like to find out how much the average price of all of our products are.
select sum(price) / count(*) as averagePrice, count(*) as totalCounted from products;
+--------------+--------------+
| averagePrice | totalCounted |
+--------------+--------------+
| 17.343333 | 9 |
+--------------+--------------+
1 row in set (0.00 sec)
The next query we will be introducing selects within selects. What's great about selects within selects is that the order of operations applies here. The select that is contained within the parenthesis () will execute first, return it's value and the rest of the main select will finally execute.
We'd like to find the differential between the actual price of each product and the average price of all products. We'll order from lowest to highest difference. The next query will take care of that.
Let's break this query down:
select
id,
description,
price,
-- calculate the average price from all products and return as averagePrice
(
-- calculate the total of all products using sum, count all found products,
-- and then create an average price
select ( sum(price) / count(*) ) from products
) as averagePrice,
-- calculate the differential between the averagePrice and the price
-- first get the average price of all products, then subtract that price from
-- the actual price to create the differencial
price - (
select ( sum(price) / count(*) ) from products
) as differential
from
products
order by
differential;
And a one liner with output for the above:
mysql> select id, description, price, ( select ( sum(price) / count(*) ) from products ) as averagePrice, price - ( select ( sum(price) / count(*) ) from products ) as differential from products order by differential;
+----+-------------------------------------------+-------+--------------+--------------+
| id | description | price | averagePrice | differential |
+----+-------------------------------------------+-------+--------------+--------------+
| 7 | Hot Slippery Lube | 2.56 | 17.343333 | -14.783333 |
| 8 | Rainbow Clown Suit | 5.95 | 17.343333 | -11.393333 |
| 9 | Clown Wig | 7.95 | 17.343333 | -9.393333 |
| 5 | Blow Up Balloon Animal Balloons | 8.95 | 17.343333 | -8.393333 |
| 6 | Hand Cuffs | 10.88 | 17.343333 | -6.463333 |
| 1 | Large Floppy Toothbrush With Suds | 19.95 | 17.343333 | 2.606667 |
| 2 | Large Floppy Clown Shoes | 19.95 | 17.343333 | 2.606667 |
| 3 | Vibra-Glove Trick Buzzin Glove | 29.95 | 17.343333 | 12.606667 |
| 4 | Sense-attentive Electrocute Your Friends! | 49.95 | 17.343333 | 32.606667 |
+----+-------------------------------------------+-------+--------------+--------------+
9 rows in set (0.00 sec)