SQL help

zkissane · **Posted:** Sun 10-29-2006 10:15PM

Suppose I have a table that shows trips to Wal-Mart. It keeps track of who went, when he/she went, and how much he/she spent:

Code:

name   date         amount
-----------------------------
Alice    1/1/06      $45.34
Bob     1/2/06      $2.72
Charles 1/2/06     $3.14
Alice    1/5/06      $42.42
Charles 1/10/06   $105.32
Bob     1/15/06    $88.00

Suppose that I then want to make a view (or a query, whatever) with 2 columns: the name of each person and the average amount that he/she spent over all of his/her trips. For this data set it would look like:

Alice 43.88
Bob 45.36
Charles 54.23

When I tried a query like

Code:

SELECT name, ( (SELECT amount FROM trips) / (SELECT CAST(COUNT(name) AS FLOAT) ) ) AS average FROM trips USING name GROUP BY name; 

it produces the average amount spent per trip for every entry in the table, like so:

Code:

name   average
-------------------
Alice        $47.82
Bob         $47.82
Charles    $47.82

How can I get it to do the math for each person individually? You can assume the name column is foreign-keyed to a "people" table if that's necessary.

EDIT: Well, I figured out at least one way to do it:

Code:

SELECT DISTINCT people.name,
( (SELECT ( SUM (trips.amount) ) WHERE (people.name = trips.name)) / (SELECT ( CAST (COUNT(trips.name) AS FLOAT) ) ) AS average
FROM people INNER JOIN trips ON (people.name = trips.name); 

SQL help

Who is online