Group data by values and draw a pie chart.

Hi all,

I have a sql table containing values,
and I need to draw a pie chart with intervals.

For example, the table contains :

0.123
1.345
5.678
...

I need to count number of values between 0 and 2 , 2 and 5, etc ...
and at the end draw a pie with the values computed.
( 345 counts between 0-2 , 234 between 2-5, etc ... )

How can I do that ?
thanks in advance.

Find more posts tagged with

Comments

mwilliams

Hi moh,

One way would be to create a computed column that checked the value of a row and assigned it a "group value". Then, in your pie chart, you could use this new "group value" column as the category and the count of the values in the category as the slice size. Let me know if you have any questions.

bhanley

You can also define the ranges in SQL and use the UNION operator to bring your aggregated results together. I have attached a sample to the thread that finds orders (from the Classic Models sample DB) and groups them in terms of order totals. You wuill see that the query groups the orders in $1000 bands. The sample then uses the data to build a pie chart.<br />
<br />
Here is the query:<br />
<br />
<pre class='_prettyXprint _lang-auto _linenums:0'>
SELECT 'Orders Up to $1000',
COUNT(*)
FROM CLASSICMODELS.ORDERDETAILS
WHERE (CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH) <= 1000
UNION
SELECT 'Orders Up to $2000',
COUNT(*)
FROM CLASSICMODELS.ORDERDETAILS
WHERE (CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH)
BETWEEN 1001 AND 2000
UNION
SELECT 'Orders Up to $3000',
COUNT(*)
FROM CLASSICMODELS.ORDERDETAILS
WHERE (CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH)
BETWEEN 2001 AND 3000
UNION
SELECT 'Orders Up to $4000',
COUNT(*)
FROM CLASSICMODELS.ORDERDETAILS
WHERE (CLASSICMODELS.ORDERDETAILS.QUANTITYORDERED * CLASSICMODELS.ORDERDETAILS.PRICEEACH)
BETWEEN 3001 AND 4000
</pre>

moh

thanks a lot for your help,
I've just finish a perl script that compute all data and
create a new table to store the result.

It's not really beautifull, So I will try the previous solutions
and let you now.

thank you.