Hi Forum,

Im in the process of creating some queries on some stock market trading data. I have a single table that contains:
StockCode, Date, OpenPrice, HighPrice, LowPrice, ClosePrice, VolumeTraded. This table is in a Schema Shares.

What I want is to query yesterdays Stock Codes, and run comparisons on the traded volumes and traded value for different periods.

The query I currently have is:

Select "StockCode",
avg(case when "TradeDate" > '1/10/2010' AND "TradeDate" < '1/1/2011' then "Volume" end ) as "2010Q4_Volume",
avg(case when "TradeDate" > '1/1/2011' AND "TradeDate" < '1/2/2011' then "Volume" end ) as "Jan_Volume",
avg(case when "TradeDate" > '1/2/2011' AND "TradeDate" < '1/3/2011' then "Volume" end ) as "Feb_Volume",
avg(case when "TradeDate" > '6/3/2011' AND "TradeDate" < '10/3/2011' then "Volume" end) as "Current_Volume",
avg(case when "TradeDate" > '6/3/2011' AND "TradeDate" < '10/3/2011' then "Volume" end) /
avg(case when "TradeDate" > '1/10/2010' AND "TradeDate" < '1/1/2011' then "Volume" end ) as "Proportion",

from "Shares"."DailyTrades"
where "StockCode" in (Select "StockCode" from "Shares"."DailyTrades" where "TradeDate" = '8/3/2011')

GROUP BY "StockCode"
HAVING 2.0* avg(case when "TradeDate" > '1/10/2010' AND "TradeDate" < '1/1/2011' then "Volume" end)
< avg(case when "TradeDate" > '6/3/2011' AND "TradeDate" < '10/3/2011' then "Volume" end)

ORDER BY "Proportion";


This query works.

However, I would like to add a column which is the last days traded value [ie. (HighPrice + LowPrice) / 2 * Volume where TradeDate = 8/3/2011]

I cant work out what I need to add to get this additional column information.



Also, is there a way to add the Column Alias to the later parts of the query, as it looks like it would need to run the same query several times to compare the results of the Averages.

Any help is greatly appreciated.