Calculating percentage

I have a table called click_statistics:

CREATE TABLE IF NOT EXISTS `click_statistics` (
  `click_id` int(8) unsigned NOT NULL auto_increment,
  `advertiser_id` int(8) NOT NULL,
  `ip_address` char(15) NOT NULL,
  `country_code` char(2) NOT NULL,
  `country_name` varchar(50) NOT NULL,
  PRIMARY KEY  (`click_id`)

I use GROUP BY to get the number of clicks coming from each country. I have two questions regarding percentage

  1. How do I calculate the percentage per country based on the total clicks
  2. How can I make it so that countries with a percentage of below 1% will be merged into one group (others)
  COUNT(*) AS `num_clicks`
  (COUNT(*) / (SELECT COUNT(*) FROM click_statistics)) * 100 AS `pct_clicks`

Or select the COUNT() from the table once in your application and calculate the percentage there, rather than in the query.

Do that in your application, you don’t need to do it in the query since the straightforward query to get the counts/percentages by country gives you all the info you need. As you loop through the rows in your application, add up all the clicks from those countries with more than 1%. When you get to a country with less than 1%, instead show your “other” row and stop looping. The number of clicks is the COUNT(*) from the whole table minus the sum you computed from the rows with more than 1%.

Hi Dan. Thank you for the response. What field should I use instead of clicks? Since I don’t have that field in my table

Sorry updated the code

just an aside…

this –

(COUNT(*) / (SELECT COUNT(*) FROM click_statistics)) * 100

will often produce a result of zero

this –

100.0 * COUNT(*) / (SELECT COUNT(*) FROM click_statistics)

will not

can you see why?

yup, that’s right, integer arithmetic


Thanks for that. I only get an error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(COUNT() / (SELECT COUNT() FROM click_statistics)) * 100 AS pct_clicks FROM ’ at line 4

I just took it as it was?

another option

SELECT @totalClicks := COUNT(*)
FROM click_statistics;
SELECT country_name,COUNT(*)/@totalClicks 
FROM click_statistics
GROUP BY country_code;

Thank you all. After some sleep and a few cups of :coffee: It works using a combination of Dan Grossman’s statement and r937’s adjustment:

  , COUNT(*) AS total_clicks
  , ROUND( 100.0 * COUNT(*) / ( SELECT COUNT(*) 
    FROM click_statistics )) as perc_clicks
    FROM click_statistics
    GROUP BY country


it also works with storing the total clicks in a variable and then it just boils down to which method is chosen to use.

glad you sorted it out in the end :slight_smile: