[prev in list] [next in list] [prev in thread] [next in thread] 

List:       sqlite-users
Subject:    Re: [sqlite] Creating Histogram fast and efficiently :)
From:       Russell Leighton <russ () elegant-software ! com>
Date:       2008-12-31 13:52:22
Message-ID: 3CEDF514-9E89-4D09-A216-E6FAF1B5D542 () elegant-software ! com
[Download RAW message or body]


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
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[prev in list] [next in list] [prev in thread] [next in thread]