Arithmetic value in sql query. 1

[Gumster]

Hello. Not very good at sql so I need some help.
Have a table with three different columns, all hourly values. Looks like this.

Date Price Volume
Sat 01.01.2000 00:00 110 1500
Sat 01.01.2000 01:00 210 1000
Sat 01.01.2000 02:00 150 4000

And so on. I would like to sum the Price/Volume arithmetic values hour by hour to weeks and months like this:
110*1500=165000+110*1000=210000+150*4000=600000 = 975000. For a week it will be about 168 values and for a month about 744. I would then like to be able to divide that value with the sum of the Volume for those hours which comes to 1500+1000+4000= 6500
975000/6500= 150

Hope someone understands what I mean .

 

[r937]

select month(yourdate)
, week(yourdate)
, sum(price*volume) / sum(volume)
from yourtable
group
by month(yourdate)
, week(yourdate)

i hasten to point out that there is no week() function in ansi sql (to my knowledge)

you will have to adjust the above to the peculiarities of your particular database if you are really serios about grouping by week

oracle has an ISO week function but in other databases you may have to do something more complex

anyhow, it all depends on how you want to group dates into weeks and what other information (perhaps a calendar table?) you have available


rudy

 

[Gumster]

Thanks for the reply! I could only get the sum(price*volume) / sum(volume) working without the month or week function, would like to get both of them to work but if it's as complicated as you say with the weeks then I can settle for the monthly function to start with.

My dates look like I posted in the original, is that the problem why the grouping by month doesn't work?

I might have posted this in the wrong forum? I'm using MS Access as the DB right now, just for testing and experimenting. Are there big differences with ANSI SQL queries?
Sorry if it's in the wrong place, I'm new to this excellent forum

 

[r937]

month() works in access, so it may be that your dates are not in a datetime column

if it's a character column, you could extract the MM.YYYY substring and group on that

rudy

 

[Gumster]

I checked and it's not in a date/time column, tried changing that but the format isn't accepted. How can I do as you suggest? The dates are in a text column.

Will I get the arithmetic value for every month between the selected timeperiod when I get this query to work? If I choose from 01.2000 - 12.2000 will I get one value for January, one for February and so on?

Thanks again for you're help...

 

[r937]

if you really wish to convert the column to datetime, define a "temp" table with a datetime column and run the old data into it

insert into newtable
( datetimefield
, othercolumns )
select mid(datefield,11,4)&'-'
& mid(datefield,5,2)&'-'
& mid(datefield,8,2)&' '
& mid(datefield,16,5)
, othercolumns
into temptable

once this works, empty the original table (delete all rows), change the field to datetime, then copy all the rows back from the temptable

note: you will probably have to change whatever application programs are currently populating the table, so that they insert dates using this format:

#2002-03-05 08:00#

you don't have to say which day of the week, that is inherent in the date

rudy