patternsqlMinor
sqlite calculating percentage
Viewed 0 times
calculatingsqlitepercentage
Problem
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.
For example, if I had a table of names, I would like to select the names that appeared less than 5% of the total.
Solution
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 ;Code Snippets
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 ;Context
StackExchange Database Administrators Q#160584, answer score: 6
Revisions (0)
No revisions yet.