4

I would like to select items from a table where their frequency count is less than 5% of the total amount.

For example, if I had a table of names, I would like to select the names that appeared less than 5% of the total.

2 Answers 2

6

A simple GROUP BY would suffice I think:

SELECT name, 
       1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) AS percentage
FROM names
GROUP BY name
HAVING 1.0 * COUNT(*) / (SELECT COUNT(*) FROM names) < 0.05 ;
2
  • is there a way to display the percentage?
    – james
    Jan 10, 2017 at 16:36
  • Sure, edited the query. Jan 10, 2017 at 16:40
1

In some cases multiplying by 1.0 may not work casting is more reliable

SELECT name, 
       (cast COUNT(*) as real)/ (SELECT cast (COUNT(*)as real) FROM names) AS percentage
FROM names
GROUP BY name
HAVING (cast COUNT(*) as real)/ (SELECT cast (COUNT(*)as real) FROM names);
2
  • Interesting. Could you provide an example of when multiplying by 1.0 wouldn't work?
    – Andriy M
    Aug 2, 2017 at 6:09
  • He probably means if the column isn't a numeric type Feb 19, 2020 at 22:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.