structure: category, name, country, crates, cost
fruit, banana, UK, 100, 1.50
fruit, banana, US, 200, 1.30
fruit, apple, US, 150, 2.58
veg, potato, UK, 25, 1.00
veg, potato, FR, 75, 4.50
veg, potato, DE, 150, 3.50
veg, carrott, FR, 33, 8.10
herb, corriander, UK, 25, 2.00
herb, parsley, SP, 200, 5.23
So I know how to select all the bananas and list their details. But I want a report which will select each category in turn and list each name underneath it.
FRUIT....CRATES..AVGCOST
Banana____300____1.40
Apple_____150____2.58
VEGETABLES..CRATES..AVGCOST
Potatoe_____250_____5.37
Carrot______33______8.10
VEGETABLES..CRATES..COST
Corriander__25______2.00
Parsley_____200_____2.00
For each category (fruit, veg, herbs), find a group with the same name (bananas, apples) within that group sum all the crates and averge the price. Display.
I'm currently doing this with PHP and a flat file database and its really tricky and inefficient. I'm hoping MySQL has some kind of magic command which does this :-)
SELECT *
FROM myTable
ORDER BY type ASC, item ASC
This will group everything from your table by the type (i.e. Fruit, Veg, Herb) and sort it alphabetically. And then within that, it'll sort the items alphabetically as well.
If you want only Veg and Herbs, then you'd do this:
SELECT *
FROM myTable
WHERE type = 'veg' or type = 'herb'
ORDER BY type ASC, item ASC
Then you could go to sort veggies by region and put them with the most expensive ones first in each region, you'd do:
SELECT *
FROM myTable
WHERE type = 'veg'
ORDER BY region ASC, price DESC
Then you've just got to get it all to show on your page. :)
G.
When I do that I get thousands of records returned.
The report I am after should look like this:
FRUIT....CRATES..AVGCOST
Banana____300____1.40
Apple_____150____2.58
Taking it back a step. If I knew that I only had bananas in the database then I'd just want a summary total for the banana's:
Banana____300____1.40
That should be easy enough. But I don't know how many different fruits there are. So the report should scan for all 'fruits' and print the summaries for each fruit.
1) Display Heading FRUIT....CRATES..AVGCOST
2) For each fruit in the database sum the crates and average the cost.
3) Display each fruit with its summary figures.
FRUIT....CRATES..AVGCOST
Banana____300____1.40
Apple_____150____2.58
Just like summary group reports in Excel.
The next step then, is to do the full report for all foods, i.e. do a summary for eac food type:
VEG.....CRATES..AVGCOST
ddsfdsdsf
sdfsdfds
JUICE....CRATES.AVGCOST
asdsadsa
asdsaddsa
Cheers.
1) select records from the main table and create a temporary heap table with the results
2) for each category select from the the heap and calculate values.
create temp type=heap select * from main_table where [user selected filters....
calc_data("Herbs")
calc_data("Fruit")
.
.
.
function calc_data($category) {
$results=mysql_query("select $category,sum(if(somefilterhere=1,1,0)),avg(cost),count(*) from temp group by $group"):
print $category;
while ($row=mysql_fetch_row($results)) {
some calcs here...
print $row[0] . $row[1] ...etc.
}
So that was it in the end. What I didnt understand initially was how to use 'subselects'. You can't in the current version so you have to create a temporary table and work on that.
The next problem I had to overcome was the summing in the select command for certain filters. For this, you use the if(expr1,expr2,expr3) function which *really* is useful.
Lastly, to speed things up, I put the temp table into a heap because this creates the table in RAM and not on the disk. Because we're just reading the data and not writing to it, we don't have to worry about losing data etc. And the speed gain is tremendous as the disk doesn't have fight with other processes.
select name,sum(crates),avg(cost) from mytable where category='fruit' group by name;
That should give you the report for category fruit....
