Wanna use AVG() but never used before...

[Shrum]

Let me see if this is possible:

I have a database that contains a number of records.

In these records, there is 1 field that contains a number.

I want to create a query that returns all the records and fields + 1 additional field (let's call it "Avg&quot that will hold the AVG() of the field that I specify; I guess it would start off like:

Select (*, AVG(`hits`) as `Avg`)

...where `hits` is the numeric field that I want to run the total average on. Granted, I know that this query alone won't give me what I want but as I stated in the title, I'm new to this function (semi-new with SQL) but I figure this should be possible. Here's a sample data block:

==========
name|hits
sean|4
rick|8
martha|1
diana|7
==========

The total of hits is 20 so the avg is based on that. After running the query I envision, I would like to see:

==========
name|hits|avg
sean|4|20
rick|8|40
martha|1|5
diana|7|35
==========

If anyone knows the correct SQL syntax...please let me know.

TIA

=============================
Sean Shrum
Shrum Consulting

http://consulting.shrum.net

 

[swampBoogie]

select name,hits,hits*(select avg(hits) from t)
 from t

You need mysql 4.1 in order for this to work. If you don't have 4.1 I see no other solution than running two queries.