create table numbers (val integer);
insert into numbers values (1);
insert into numbers values (5);
....
sqlite> select * from numbers order by val;
1
5
10
12
12
15
20
20
20
select case when val < 10 then 1 when val >=10 and val < 20 then 2
else 3 end as bin,
count(1) as c
from numbers group by bin;
sqlite> select case when val < 10 then 1 when val >=10 and val < 20
then 2 else 3 end as bin,
...> count(1) as c
...> from numbers group by bin;
1|2
2|4
3|3
On Dec 31, 2008, at 6:16 AM, Jonathon wrote:
> Hello all,
>
> I have a column of numbers in a table, and I was wondering if it is
> possible
> to create a histogram out of it fast and efficiently?
>
> For example, assuming the data in the column is: 1, 5, 10, 12, 12,
> 15, 20,
> 20, 20.. I would like to return:
>
> 'less than 10' --> 2
> 'less than 20 and greater than or equal to 10' --> 4
> 'great than or equal to 20' --> 3
>
> I hope that makes sense. I checked all over Google, and it seems that
> different databases seem to tackle this problem differently. So.. I
> was
> just curious how sqlite can help me make this calculation fast :)
>
> Thanks,
> J
> _______________________________________________
> sqlite-users mailing list
> [email protected]
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
