| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2007-01-15 : 11:06:46
|
| Ivan writes "Hello,I am very new to SQL so this may be super easy, or rediculously complex... not sure.I have a database with a bunch of customers and their total purchase (as well as other junk)on each instance. I want to append a new column to the database that is equal to the average purchase each person makes.In other words the current db looks like:(user ID), (purchase amount), (date)1, 2, 1/1/011, 6, 1/3/011, 10, 1/10/012, 52, 1/4/012, 32, 1/7/013, 12, 1/1/01user 1 made 3 purchases of 2, 6, and 10 dollars. That is 18 dollars over 3 purchases so 6 dollars on average.The new db would be:(user ID), (purchase amount), (date), (average purchase)1, 2, 1/1/01, 61, 6, 1/3/01, 61, 10, 1/10/01, 62, 52, 1/4/01, 422, 32, 1/7/01, 423, 12, 1/1/01, 12how do I get SQL to calculate the average purchase for each customer and insert that value into every instance of the customer's average purchase field?the database is static, in that no more data will be added, so a one time aggregation dump is what I need.Thanks for the help,Ivan" |
|
|
Vijaykumar_Patil
Posting Yak Master
121 Posts |
Posted - 2007-01-15 : 11:38:27
|
| INSERT INTO NewdB([User ID],[purchase amount],[date])SELECT [User ID],[purchase amount],[date]) FROM olDBSELECT [user ID] ,(sum(purchaseamount))/count(purchaseamount)as [average purchase] INTO temp_average FROM olDBGROUP BY [user ID]UPDATE NewdbSET [average purchase] = b.[average purchase]FROM newdb aJOIN temp_average bON a.[user ID]= b.[user ID]DROP TABLE temp_averageThis should give you the desired results.Necessity is the mother of all inventions! |
 |
|
|
|
|
|
How to take average and append to database