Newbie question re SQL
От | john@localhost.localdomain (John) |
---|---|
Тема | Newbie question re SQL |
Дата | |
Msg-id | slrna7triq.b2u.orangefree89@localhost.localdomain обсуждение исходный текст |
Ответы |
Re: Newbie question re SQL
|
Список | pgsql-general |
I have created a PostgreSQL database to keep track of my investments. I have created one table to track my purchases and sales of securities. One piece of information I need to be able to extract from the database is the cost of each security. Calculating the cost of each security if I have only purchased that security is easy: SELECT quantity,price,quantity*price AS cost FROM transactions; But what if I have bought *and* sold the security? Let's say I bought 300 iShares on December 15, 2001 at $125 per share, 500 iShares on January 1, 2002 at $135 per share, and then I sold 100 iShares on February 15, 2002 at $110 per share. I can calculate my cost by hand easily enough, and the result would like the following: Quan Price Cost ==== ===== ==== 300 125 37,500 500 135 67,500 (100) 110 (13,125) ----- -------- 700 91,875 How do I get SQL to do this. I tried using a CASE statement. If quantity > 0, then use the SELECT statement above. If quantity < 0, then find the average cost using all transactions from the date before the date of the current transaction and then multiply that by the negative quantity. That didn't give me the right number. Does anyone have any suggestions? Assume there will be multiple purchases and sales for each security tracked by the database.
В списке pgsql-general по дате отправления: