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`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;

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)
SELECT 
  country_code,
  COUNT(*) AS `num_clicks`
  (COUNT(*) / (SELECT COUNT(*) FROM click_statistics)) * 100 AS `pct_clicks`
FROM
  click_statistics
GROUP BY
  country_code
ORDER BY
  COUNT(*)

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

:slight_smile:

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:


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

:tup:

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: