Jake Stride wrote:
> I'm not sure if there is a way to do this without using a programming
> language/function ie a for loop for example. Ideally I would like to
> do it all in postgresql
>
> I have 2 tables, for example:
>
> Item
> ====
> id
> name
> cost
> sold_date
> item_type_id
>
> Item Type
> =========
> id
> item_type
>
> Now I want to make a report whereby I sum the cost of each item type
> per month (within a range) so I end up with the following:
>
> 01/04 02/04 03/04 04/04 05/04 06/04 ... etc
> item_type A
> item_type B
> item_type C
> .
> .
> .
> etc
>
> THe problem is that I don't know how many item types or months I need
> to do it for (obviously it will be for all item types in the item type
> table, but could be for 1 month or 100 months!). The only way I can
> see of doing it is summing the total cost per item for a month, then
> repeating that query for each month in the actual application is this
> correct or is there a way to do it natively?
>
> Many Thanks
>
> Jake
I've done something similar, but I use a function and a view to get the
results, and the report columns and rows are transposed from how you
would like it, ie the dates are along side and the items are along the
top. The items are coded directly into the function and view
definitions, but the dates and counts are dynamically updated, so if you
were to use this you would manually have to update the function and vew
to accomodate them. If you're happy with that I could send you a copy
of the function and view, which with only a little modification on your
part, could give you results.
Ron