Many times we need to get a statistical distribution of values in a database table. Say you have a e-commerce shoe store having a product table with the following fields and values. As this is only an example I’ve limited the table to a few items; there will hundreds of rows in a real-life table.
;'product' table
;
+----+----------+-------+------+-------+
| id | type | color | size | price |
+----+----------+-------+------+-------+
| 1 | sandals | black | 7 | 45.00 |
| 2 | boots | black | 8 | 36.00 |
| 3 | loafers | brown | 8 | 45.00 |
| 4 | sandals | brown | 7 | 45.00 |
| 5 | sneakers | blue | 6 | 25.00 |
| 6 | sneakers | black | 6 | 25.00 |
| 7 | sandals | blue | 7 | 45.00 |
| 8 | loafers | black | 7 | 35.00 |
| 9 | boots | brown | 8 | 42.00 |
+----+----------+-------+------+-------+
What we want is to get a distribution which lists how many of each value we have got, like: sandals = 3, boots = 2, size 7 shoes = 4 etc.
The following query will accomplish that.
SELECT 'type', product.type, COUNT(*) AS total_rows
FROM product GROUP BY product.type
UNION
SELECT 'color', product.color, COUNT(*) AS total_rows
FROM product GROUP BY product.color
UNION
SELECT 'size', product.size, COUNT(*) AS total_rows
FROM product GROUP BY product.size
UNION
SELECT 'price', product.price, COUNT(*) AS total_rows
FROM product GROUP BY product.price ;
This will return the following results.
+-------+----------+------------+
| type | type | total_rows |
+-------+----------+------------+
| type | boots | 2 |
| type | loafers | 2 |
| type | sandals | 3 |
| type | sneakers | 2 |
| color | black | 4 |
| color | blue | 2 |
| color | brown | 3 |
| size | 6 | 2 |
| size | 7 | 4 |
| size | 8 | 3 |
| price | 25.00 | 2 |
| price | 35.00 | 1 |
| price | 36.00 | 1 |
| price | 42.00 | 1 |
| price | 45.00 | 4 |
+-------+----------+------------+
We now know how many of each type we have. This will be helpful to carryout any statistical analysis on the products table. We can create a new table (product_stats) with the above query by adding a ‘INSERT’ statement.
INSERT INTO product_stats (column_name, value, total_rows)
SELECT 'type', product.type, COUNT(*) AS total_rows
FROM product GROUP BY product.type
UNION
SELECT 'color', product.color, COUNT(*) AS total_rows
FROM product GROUP BY product.color
UNION
SELECT 'size', product.size, COUNT(*) AS total_rows
FROM product GROUP BY product.size
UNION
SELECT 'price', product.price, COUNT(*) AS total_rows
FROM product GROUP BY product.price ;