Top-X of data of MySQL db

[thijsnetwork]

While coding PHP I enountered the following problem, I'm using a MySQL database to store the platform of each user, like:

+-----------+-------------------------------------+
¦ ip  ¦ platform       ¦
+-----------+-------------------------------------+
¦ xx.x... ¦ Windows        ¦
¦ xx.x... ¦ Linux        ¦
¦ xx.x... ¦ Windows        ¦
¦ xx.x... ¦ Windows        ¦
¦ xx.x... ¦ Mac         ¦
¦ xx.x... ¦ Mac         ¦
+-----------+-------------------------------------+

How can I construct a MySQL query which calculates the percentage of users who use each operating system, so I get results like:

1. Windows (50%)
2. Mac (33%)
3. Linux (0,16%)

[lorax]

MySQL does not provide for percentages via a SELECT query. You're best bet is to group the data by platform (or whatever metric you choose to analyze) and do the calculations via PHP.

[thijsnetwork]

Can you give an example of how to do this? Suppose that I don't know which platforms there are, so that I can't check if for example the platform Linux is.

[lorax]

You might find this new series of articles on MySQL.com usefull. It's all about sub-Queries and might be spot on with what you need.

[mysql.com...]

[thijsnetwork]

the content of that article is far too complex, i'd like to use a straight to the point solution without using sophisticated new functions of mysql. It's no problem to combine the functionality of php and mysql to solve the problem. Any idea's?

[jatar_k]

maybe this will be a little more straight forward

Counting Rows with MySQL [mysql.com]

If you get each total you can then use php to turn those into percentages.

[lorax]

>> straight to the point solution without using sophisticated new functions of mysql

Well.. they aren't new functions but I know what you mean. ;)

The only problem with using COUNT is that we don't know how many distinct platforms there are to begin with and then we'll need to get the names of each platform to display along with the percentage. But I think some combination of COUNT and DISTINCT [mysql.com] should fix you up.

[jatar_k]

>>I think some combination of COUNT and DISTINCT should fix you up

pretty much the same thing I was thinking

[lorax]

I suspected as much. :)

[thijsnetwork]

i've already figured it out using 2 queries, one for counting the total rows. The other query groups all possible operating systems like:

SELECT COUNT(*) AS number .... GROUP BY platform ..

and php calculates the percentages of each platform.

[lorax]

thijsnetwork,
As usual, there is more than one way to accomplish the goal. Glad you got it sorted.