CODEWITHSUNDEEP
mysql
dvlper
dvlper

Reputation: 472

Selecting based on a list MySQL

I have two table (devices) and (os) described as:

os:
+-----------+---------------+-------------+
| device_os | os_name       | device_type |
+-----------+---------------+-------------+
| devos_10  | Funtoo Linux  | devtype_5   |
| devos_101 | Windows 10    | devtype_4   |
| devos_102 | Windows Vista | devtype_4   |
| devos_103 | Mac OS X 10.4 | devtype_6   |
......

devices
+------------+-----------+-------------+-------------+
|  device_id | device_os | device_type |   country   |
+------------+-----------+-------------+-------------+
| id_804397  | devos_10  | devtype_5   | country_146 |
| id_1274047 | devos_103 | devtype_6   | country_16  |
| id_4416554 | devos_102 | devtype_4   | country_14  |
......

When I execute following query:

SELECT a.device_os, (count(*)/42028)*100 AS value FROM devices a GROUP BY a.device_os ORDER BY value DESC;

I get, a table like:

+-----------+---------+
| device_os | value   |
+-----------+---------+
| devos_10  | 41.8578 |
| devos_102 | 40.0638 |
| devos_103 | 18.2926 |
......

but I want the result like:

+---------------+---------+
| os_name       | value   |
+---------------+---------+
| Funtoo Linux  | 41.8578 |
| Windows Vista | 40.0638 |
| Mac OS X 10.4 | 18.2926 |
....

I have tried doing:

SELECT c.os_name, a.device_os, (count(*)/42028)*100 AS value FROM devices a, os c WHERE a.device_os = c.os_name GROUP BY a.device_os ORDER BY value DESC;

But didn't get expected result. Can anyone walk me around this problem. Any help is greatly appreciated.

Upvotes: 1

Views: 34

Answers (1)

Darshan Mehta
Darshan Mehta

Reputation: 30819

Try the following query:

SELECT o.os_name, (count(a.*)/42028)*100 AS value 
FROM devices a join os o on a.device_os = o.device_os 
GROUP BY a.device_os ORDER BY value DESC;

Upvotes: 2

Related Questions