Summarizing data into another table 2

[Iasthaai]

Hi everyone and thank for reading my question, here it is:

I have a table as follows

stocktrades {
        symbol     Text
        shares     Integer
        price      Double
        timestamp  Long Integer   (this is presented in hhmmss format but not using any mask.)
 
}

And I wish to summarize the data from table stockrades which contains data throughout a single day into this following table:

averageprice {
         symbol     Text
         period     Integer (Contains 0 for interval of 000000 to 005959 of timestamp field in above table and goes
                             like this until 23 = 230000 to 235959.)
         avgprice   Double  (this contains the weighted average price or (price*shares) / the total number of shares
                             traded in any interval for this stock.)
}

I'm unsure of whether I am going to have to write 24 separate lines in SQL to migrate this data for each interval of the day. But even more importantly, I'm unsure of how to tackle this problem.

So far I have:

SELECT symbol AS symbol, SUM(price*shares) AS avgprice INTO averageprice
FROM stocktrades;

Which really doesn't solve any issue I'm having...it just proves to me that at least the summation is indeed working.

Even if you can't give me the whole answer, any helpful hints will defintely be appreciated. I'm using MS Access to test these queries by the way.

 

[JarlH]

Untested:
[tt]
INSERT INTO averageprice
SELECT symbol,period,SUM(shares*price)/SUM(shares)
FROM (select symbol,shares,price,timestamp/10000 as period
from stocktrades)
GROUP BY symbol,period[/tt]

ANSI/ISO SQL-99 compliant, with "Derived tables" extension.

Note that TIMESTAMP is a reserved word, i.e. you'll maybe run into problems when upgrading if you keep that column name.

 

[PHV]

And what about this ?
SELECT symbol, TRUNCATE(timestamp/10000,0) period, SUM(shares*price)/SUM(shares) avgprice
FROM stocktrades
GROUP BY symbol, TRUNCATE(timestamp/10000,0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Iasthaai]

Thanks guys for the responses. I tried both queries and JarlH, your untested query comes back with an error that I am currently trying to sort out. PHV, this one looks really useful but for some reason my MS Access doesn't like the TRUNCATE function....which is a function I REALLY need. I am using Access 2003 and without this truncate function my results come back in the form of: 015659 = 01.5659 which is NOT what I want. I need 015659 to be just 1 without any decimal places.

Also the avgprice is the really tricky part because I have to take each specific symbol from the stocktrades table, condense each hour into the interval denoted by either 0 through 23 and get the SUM(price * shares) for each entry in that interval. Now after that is done I must divide the above SUM(price * shares) by every shares traded for that symbol for the entire day. So, to summarize what I just said:

avgprice = SUM(price * shares) for each specific entry in an hour interval / SUM(shares) for EVERY ENTRY FOR THAT SYMBOL (including those outside the denoted interval of time).

This is weighted average. According to a stock site I went to:
? Number of shares bought * Share price
-----------------------------------------
Total Shares Bought

 

[Iasthaai]

Sorry, in the above message that ? at the bottom was supposed to be a Sigma.

 

[PHV]

As you posted in the ANSI SQL forum I gave you an ANSI SQL solution.
For Access (JetSQL):
SELECT symbol, timestamp\10000 AS period, SUM(shares*price)/SUM(shares) AS avgprice
FROM stocktrades
GROUP BY symbol, timestamp\10000


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Iasthaai]

I had no idea that switching the division symbol (/) to (\) made it so that the decimal places were truncated in MS Access. Thanks PHV! By the way, I thought MS Access was in the ANSI SQL family but I guess I was wrong. Sorry about that, but thanks for the help anyway, you've cleared a lot of questions up for me!