Mike DelGaudio

Here is the easiest way I know to create the data for a histogram chart.

Consider the following query.
"select count(price - (price mod 10)) as freq, price - (price mod 10) as label
from sales
where 1
group by price - (price mod 10)
order byprice - (price mod 10) ASC")


In this query, we are creating a histogram of items according to their list price. We will be able to see the distribution of products , in this case, in strata of 10. In this case the price of products would be distributed over prices between, say 0 and 2 or 3 hundred. You can set a MOD value that would create strata in reasonable segments for your data.

What we are doing here, is using the MOD function to round down the individual sales to increments set by the MOD value. MOD is a type of division that only returns the remainder.

For example if you have sales of
10
11
23
24
45
56

MOD 10 of each of those values would return:

0
1
3
4
5
6

We then subtract the remainder from the price, resulting in

10
10
20
20
40
50


Rather than just outputting that list, we count each of those values to get a distribution:


+---------------+
| freq | label |
+---------------+
| 2 | 10 |
| 2 | 20 |
| 1 | 40 |
| 1 | 50 |
+---------------+


Finally, since we use the COUNT() function, we need to group, and we put them in order by the label so that the segments are in order.

You can then feed those values to your favorite graphing engine, to make handsome charts of your resulting data.