Pie chart statistics

[ecopetition]

Hello,

 

In a MySQL table I have the following rows:

Item nameItem price

Milk1.05

Butter1.50

Lettuce0.70

Butter1.55

Bread0.65

Chicken3.50

Milk1.00

Coca Cola1.20

Apples0.90

Toothpaste1.00

Oranges0.80

Milk1.00

 

What I'm looking to do is collect all of the milks, all of the butters, etc and give a $total_price_spent_on value for each product, so for milk we'd have 3.05, for oranges we'd have 0.80, and so on until all items on the list have been totalled.  Then I want to select the most expensive 3 totals, display them in a pie chart, and all other items to be listed under an "other" label.

 

Any pointers as to how to go about this?

 

Many thanks

[Psycho]

To get the product totals in order of amount (highest to lowest) you would do a query such as this:

SELECT product_name, SUM(product_cost) as product_total
FROM tableName
GROUP BY product_cost
ORDER BY product_total DESC

 

Then when processing the record set you could add up the totals for the other items. Alternatively you could set a limit on that query to only get the top three products and do a second query to get the grand_total (either excluding the top three or subtracting the top three totals after you get the results).

 

As for creating a pie chart, there are many solutions available. Just do a google search for "PHP Pie Chart".

[litebearer]

Might look here

http://www.tizag.com/mysqlTutorial/mysqlsum.php

[ecopetition]

Thanks for your replies, this was very helpful.