1

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

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 ;
  • is there a way to display the percentage? – james Jan 10 '17 at 16:36
  • Sure, edited the query. – ypercubeᵀᴹ Jan 10 '17 at 16:40
0

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);
  • Interesting. Could you provide an example of when multiplying by 1.0 wouldn't work? – Andriy M Aug 2 '17 at 6:09

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.