Getting percentage of "Count(*)" to the number of all items in "GROUP BY"
- by celalo
Let's say I need to have the ratio of "number of items available from certain category" to "the the number of all items". Please consider a MySQL table like this:
/*
mysql> select * from Item;
+----+------------+----------+
| ID | Department | Category |
+----+------------+----------+
| 1 | Popular | Rock |
| 2 | Classical | Opera |
| 3 | Popular | Jazz |
| 4 | Classical | Dance |
| 5 | Classical | General |
| 6 | Classical | Vocal |
| 7 | Popular | Blues |
| 8 | Popular | Jazz |
| 9 | Popular | Country |
| 10 | Popular | New Age |
| 11 | Popular | New Age |
| 12 | Classical | General |
| 13 | Classical | Dance |
| 14 | Classical | Opera |
| 15 | Popular | Blues |
| 16 | Popular | Blues |
+----+------------+----------+
16 rows in set (0.03 sec)
mysql> SELECT Category, COUNT(*) AS Total
-> FROM Item
-> WHERE Department='Popular'
-> GROUP BY Category;
+----------+-------+
| Category | Total |
+----------+-------+
| Blues | 3 |
| Country | 1 |
| Jazz | 2 |
| New Age | 2 |
| Rock | 1 |
+----------+-------+
5 rows in set (0.02 sec)
*/
What I need is basically a result set resembles this one:
/*
+----------+-------+-----------------------------+
| Category | Total | percentage to the all items | (Note that number of all available items is "9")
+----------+-------+-----------------------------+
| Blues | 3 | 33 | (3/9)*100
| Country | 1 | 11 | (1/9)*100
| Jazz | 2 | 22 | (2/9)*100
| New Age | 2 | 22 | (2/9)*100
| Rock | 1 | 11 | (1/9)*100
+----------+-------+-----------------------------+
5 rows in set (0.02 sec)
*/
How can I achieve such a result set in a single query?
Thanks in advance.
