HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

sqlite calculating percentage

Submitted by: @import:stackexchange-dba··
0
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.

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.