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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

Jake Stride wrote:


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


---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Ron St-Pierre wrote:

aren't an issue as I can adjust them when I get them from the database
and draw the graph. The problem I may have is that I don't know how many
types there will be as people can add/remove them from the 'type' table
as they please, but I may be able to work something out with what you
have done already.

Thanks

Jake

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

Jake Stride wrote:

I'm not sure that this is what you're looking for, but if it isn't it
may just provide you with a starting point to solve it. Okay, here it
is. My function just uses a count of things, you could modify it to do a
sum of sales. I haven't tried but you could probably get the function to
generate the items too. Also, change sales_date to month, etc, and you
would need an itemSummary table with fields sold_date, item01, etc.

Anyway, this might be a starting point for you to solve your problem.

Ron


CREATE OR REPLACE FUNCTION updateItemSummary() RETURNS void AS '
DECLARE
compDate DATE;
currCount INT;
BEGIN
compDate := current_date;
LOOP
DELETE FROM itemSummary WHERE sold_date = compDate;
INSERT INTO itemSummary (sold_date) VALUES (compDate);
UPDATE itemSummary
SET item01 = (SELECT count(*) FROM sales_data WHERE
sold_date = compDate AND item_type_id = 55),
item01 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 59),
item02 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 48),
item03 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 36),
item04 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 96),
item05 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 99),
item06 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 98),
item07 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 97),
item08 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 96),
item09 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 95),
item11 = (SELECT count(*) FROM sales_data WHERE sold_date
= compDate AND item_type_id = 93),
dow = to_char(compDate::timestamp, Dy)
WHERE compDate = sold_date;
compDate = compDate - interval 1 day;
IF compDate = current_date - interval 15 days THEN
EXIT;
END IF;
END LOOP;
RETURN;
END;
' LANGUAGE 'plpgsql';

SELECT updateItemSummary();


CREATE VIEW currentstats
AS SELECT DISTINCT ON (sold_date)
sold_date AS date,
dow AS "day",
item02,
item03,
item04,
item05,
item06,
item07,
item08,
item09,
item10
FROM itemSummary
ORDER BY updatedate DESC
LIMIT 15;


date | day |item01|item02|item03|item04|item05 |item06| item07 |item08| item09|item10

------------+-----+------+------+------+------+---------+------+----------+------+-------+------

2004-10-13 | Wed | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 1878 | 1878

2004-10-12 | Tue | 1893 | 2142 | 3599 | 1310 | 1721 | 3558 | 16904 | 1073 | 17978 | 17978

2004-10-11 | Mon | 0 | 0 | 3598 | 1313 | 991 | 3558 | 16867 | 942 | 1542 | 1542

2004-10-10 | Sun | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2004-10-09 | Sat | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2004-10-08 | Fri | 1893 | 2142 | 3599 | 1307 | 1747 | 3558 | 16978 | 1076 | 1272 | 1272

2004-10-07 | Thu | 1893 | 2141 | 3600 | 1312 | 1724 | 3557 | 16961 | 1075 | 643 | 643

2004-10-06 | Wed | 1892 | 2141 | 3598 | 1310 | 1777 | 3555 | 16979 | 1075 | 2356 | 2356

2004-10-05 | Tue | 1890 | 2140 | 3596 | 1308 | 1747 | 3553 | 16892 | 1075 | 343 | 343

2004-10-04 | Mon | 1888 | 2141 | 3599 | 1308 | 1731 | 3552 | 16934 | 1075 | 132 | 132

2004-10-03 | Sun | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2004-10-02 | Sat | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0

2004-10-01 | Fri | 1888 | 2144 | 3599 | 1310 | 1667 | 3552 | 16957 | 1073 | 94 | 94

2004-09-30 | Thu | 1880 | 2144 | 3603 | 1311 | 1723 | 3557 | 16802 | 1068 | 294 | 294

2004-09-29 | Wed | 1879 | 2144 | 3604 | 1309 | 1792 | 3560 | 16907 | 1072 | 285 | 285



Ron


---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)