SQL/coldfusion geniouses HELP!

[dmurray14]

OK guys, I need some quick SQL help. I have a DB full of plant availabilities, broken down into type, size, row, and column, and I need to create a report formatted like this:

Plant Type
....................Size
................................Quantity
....................Size
................................Quantity

Plant Type
....................Size
................................Quantity

...if that makes sense. Basically, ordered by Plant name first, then by size, then the quantity of that size available. The only problem is, All of the plants are strewn across the database, split into rows. So for instance, one record would have Plant Type, Size, Block, Row, Quantity. All of these need to be combined into a report. No need for location on the report, just totals for the sizes and quantitys. Hopefully this makes sense. Basically, how do I get Coldfusion to loop first by plant name, then by size, then give the total quantitiy of that particular size?

Please help!

Thanks,
Dan

 

[rh71]

How do you intend for CF to generate a "report" ? Or did you mean just sums of those fields displayed on a webpage ?

 

[dmurray14]

Yeah, exactly, on a web page. I can get so far as to make a list of all the plant names, but within them I need the different sizes that plant comes in, and the total amount of them in stock.

Please help!

Dan

 

[Mr.IncrediblyBored]

Something like this?

SELECT plant_type, size, SUM(quantity)
FROM plant_information
GROUP BY plant_type, size
ORDER BY plant_type, size;

Do you need the SUM() of the sizes for the plant type as well? That would require a nested select and shouldn't be too difficult.

 

[rh71]

man, it's been so long since I've had to use CF and I don't have any test data to play with... did you already try CFLOOP QUERY ... or CFLOOP COLLECTION ?

 

[Blieb]

I don't do CF ... but IIRC you just do CFOutput and put however you want it formatted!

It was a piece of cake.

The totals might be a separate query ... but I'm sure there's a way in CF to do it ...