CODEWITHSUNDEEP
sqlsqlitegroup-bycountpercentage
YoYoYo
YoYoYo

Reputation: 439

Get percentages of each values counted in SQLite?

I have all lottery draws stored in a SQLite database. I am using this query to count how many times numbers appears in last 150 draws to present:

SELECT LAST_150, COUNT(LAST_150) FROM CalculatedNumbers GROUP BY LAST_150 ORDER BY COUNT(LAST_150) DESC;

LAST_150 is a column in my table named CalculatedNumbers which store how many times a given number can be found in last 150 draws to the present. It gives me this result:

enter image description here

Which is read as follow: In last 150 drawn 18002 numbers are drawn 37 times. 17882 numbers are drawn just 38 times in Last 150 draws and so on.

How to do it to show another column which gets the percentages for each population of numbers. e.g. x% means 18002, x% means 17882, x% means 17352 from the total numbers count?

Thank you very much in advance!

Upvotes: 0

Views: 477

Answers (1)

GMB
GMB

Reputation: 222582

You can use window functions:

select 
    last_150, 
    count(*) no_last_150,
    1.0 * count(*) / sum(count(*)) over () percent_last_150
from calculatednumbers 
group by last_150 
order by count(last_150) desc;

If your version of SQLite does not suport window functions, then:

select 
    last_150, 
    count(*) no_last_150,
    1.0 * count(*) / (select count(*) from calculatednumbers) percent_last_150
from calculatednumbers 
group by last_150 
order by count(last_150) desc;

Upvotes: 1

Related Questions