51

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.

HoldOffHunger
  • 18,769
  • 10
  • 104
  • 133
pars
  • 3,700
  • 7
  • 38
  • 57
  • I don't know how to do it in a query, but why not do it in the code that handles the result? – Bart van Heukelom Jun 17 '10 at 12:43
  • unfortunately, I need to make it in one single query to fit in my API. nevertheless, I found a similar Q&A at stackoverflow. See my answer below. – pars Jun 17 '10 at 12:52

3 Answers3

71
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.

bleeeah
  • 3,534
  • 19
  • 25
  • 2
    Yes you are right, I also found this topic: http://stackoverflow.com/questions/1576370/getting-a-percentage-from-mysql-with-a-group-by-condition-and-precision It seems like the inner query might not even take so much time even though it is repeated, thanks to MySQL optimization etc. – pars Jun 17 '10 at 12:55
  • 3
    Isn't the division supposed to be reversed in order to get a percentage? `((SELECT COUNT(*) FROM Item WHERE Department='Popular') / COUNT(*)) * 100` – alediaferia May 18 '18 at 13:14
12

This should do it:

SELECT I.category AS category, COUNT(*) AS items, COUNT(*) / T.total * 100 AS percent
FROM Item as I,
     (SELECT COUNT(*) AS total FROM Item WHERE Department='Popular') AS T
WHERE Department='Popular'
GROUP BY category;
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
the_void
  • 5,512
  • 2
  • 28
  • 34
5
SET @total=0;

SELECT Category, count(*) as Count, count(*) / @total * 100 AS Percent FROM (
    SELECT Category, @total := @total + 1
    FROM Item
    WHERE Department='Popular') temp
GROUP BY Category;

An advantage of doing it this way is you do not have to duplicate the WHERE condition, which is a ticking time bomb the next time someone comes by to update the condition, but doesn't realize it's in two different places.

Avoiding the duplicate WHERE condition also improves readability, especially if your WHERE is more complex (with multiple joins, etc).

Despertar
  • 21,627
  • 11
  • 81
  • 79