Getting percentage of "Count(*)" to the number of all items in "GROUP BY"

Question

Let's say I need to have the ratio of "number of items available from certain category" to "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.

Answer 1

SELECT Category, COUNT(*) AS Total , (COUNT(*) / (SELECT COUNT(*) FROM Item WHERE Department='Popular')) * 100 AS 'Percentage to all items',  FROM Item WHERE Department='Popular' GROUP BY Category; 

I'm not sure of the MySql syntax, but you can use a sub-query as shown.