UPDATE problem

[Lize]

Hello, I'm having some problems writing what I am sure you will think is a simple query!I have a table which has numbers in it which have two columns making them unique. All I want to do is update a further column in the same table with the average of these numbers.So the table has something like this:Day - Monday, Tuesday, Wednesday etcHour - 0, 1, 2 etcQuarter - 1, 2, 3, 4PCTotal - The numbers to get the average fromPCAverage - Hopefully to be updated!The following query returns the results I want, but I can't seem to make this work as part of the update.

SELECT     [Hour], Quarter, AVG(PCTotal) AS Expr1FROM tblUptime WHERE ([Day] <> 'XXX')GROUP BY [Hour], Quarter

This is only to be run nightly as a maintenance routine over approximately 900 records, so it doesn't even need to be very elegant.I'm obviously not an SQL programmer, and only use it for interfacing to vb.net via stored procedures to display information on a website. I've found this site pretty useful in the past so thought I would try a post.Any help would be appreciated and gratefully received.ThanksLiz

[justsomeguy]

I'm a little confused, is the PCAverage field in each row, so that one row consists of something like this:

Day      Hour  Quarter  PCTotal PCAverageMonday   4     1        7       7

Is the PCTotal field only a single number, or a list of numbers? The query you have there looks like it will calculate the averate PCTotal (one number in each field) over all rows that do not match a certain day. The easiest way to get the average is to run that query, get the average out of the result set, and then use another query to update the average somewhere else. Or am I missing something?

[Lize]

Thanks for the reply.Sorry, it's was bit difficult to explain, but you are right, the row is as you suggest but with another column PCQuotes.I am trying to update PCAverage for each row with the average of PCQuotes by days for that hour/quarter period.My select query works fine to get the average figures, my problme was in trying to calculate the average and update the PCTotal column in one query rather than running two queries and storing the results in a sepratate table, but if needs be I can use two queries as you suggest.ThanksLiz

[justsomeguy]

An uber SQL guru might be able to tell you how to do that in one query, but I think it's easier to use two.