"histogram" in sql

hello, i am pretty new to sql. i can't figure out how, given a table of a quantity obtained in many identical experiments, create a table whose data can then be used directly to plot a histogram of the results. in other words, this new table should tell me how many times the quantity was between, say, 0 and 10, 10 and 20, 20 and 30 etc.

Well you might just have a table that contains the results with an experiment_id and a count. When you get the data out to make a chart you can use the query criteria to get counts in various ranges.

If you have different things you need to keep counts for, just create a new column for each type of thing.

Does that help? Do you have a table designed already? What program or tools do you plan to use to plot your histogram?

i think i didn't phrase my question very well. let's say i have a table like this:

num value
1 5
2 11
3 6
4 25
5 24
6 29
7 38
8 17
9 15
10 11


the resulting table should look like this:

range count
0-10 2
10-20 4
20-30 3
30-49 1

what query can i use to create the second table from the first?

You would have to do a series of nested queries to get the data in one select statement (or you can create a new table, then update). It would be something like this:

How about trying:
select case value/10
when <1 then ' 0 to 10'
when <2 then '11 to 20'
when <3 then '21 to 30'
when <5 then '31 to 49' end as range, count(value)...

(Note, I didn't test it, so hopefully I didn't hopelessly mangle the case...when...then syntax. But you should get the idea.)

David

Late I know but this may help others looking to achieve this goal.

You have an array of sample statistics and you want to break them into groups of an arbitrary size. Then you want a count of the number in each group.

Using the modulus function (remainder after division) in SQL you can determine the start value of each group as:

value - mod(value, group_size)

Then you only need count(*) them and group by them.

select value-mod(value,500), count(*)
from MyFile
where My_Selection_Criteria_is_true
group by value-mod(value,500)

This produced a result in my case like:


Numeric Expression COUNT ( * )
0 480
500 290
1,000 134
1,500 247
2,000 268
2,500 287
3,000 259
3,500 230
4,000 232
4,500 221

Hope this helps!
Daz.