CODEWITHSUNDEEP
mysqlsql
oaziz
oaziz

Reputation: 1372

How can I find the ratio?

I'm building a tag cloud and since it's for a large site I'm saving the counts in a table to avoid counting everything with each request, here it is:

CREATE TABLE `counts` (
  `name` CHAR(35) NOT NULL DEFAULT '',
  `total` INT(11) NOT NULL,
  `type` tinyint(4) NOT NULL,
  `locale_id` SMALLINT(6) NOT NULL,
  PRIMARY KEY (`name`,`type`,`locale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


INSERT INTO `counts` (`name`, `total`, `type`, `locale_id`)
VALUES
    ('k3k3k',888,3,1),
    ('krkrkrkr',333,3,1),
    ('zzxzx',22,3,1);

name is the tag word and total is how many times it is mentioned in the tag table. I need to get the percentage of every tag to build the tag cloud.

How can I do that?

Upvotes: 0

Views: 2992

Answers (3)

Mithrandir
Mithrandir

Reputation: 25337

Something like this should do it:

SELECT name,(total/(SELECT SUM(total) FROM counts)) * 100 
FROM counts WHERE name = 'k3k3k'

You my want to round the result or do some other formatting ...

Upvotes: 2

Sergio Tulentsev
Sergio Tulentsev

Reputation: 230336

Normalize! That is, select max count and base all other counts on it.

SELECT name, (total / @max_count * 100) percentage
FROM counts, (SELECT @max_count := MAX(total) FROM counts) t1;

Result:

+----------+------------+
| name     | percentage |
+----------+------------+
| k3k3k    |   100.0000 |
| krkrkrkr |    37.5000 |
| zzxzx    |     2.4775 |
+----------+------------+

Or you can use SUM instead of MAX

SELECT name, (total / @max_count * 100) percentage
FROM counts, (SELECT @max_count := SUM(total) FROM counts) t1;


+----------+------------+
| name     | percentage |
+----------+------------+
| k3k3k    |    71.4401 |
| krkrkrkr |    26.7900 |
| zzxzx    |     1.7699 |
+----------+------------+

Upvotes: 1

Florin Ghita
Florin Ghita

Reputation: 17643

   select 
     name, 
     total,
     total/(select sum(total) from counts)*100 as percentage
   from counts

Upvotes: 3

Related Questions