Count, Sum, and Group By Problem 1

[Zych]

Hello,

I am fairly new to sql, at least I have been away from it for awhile. I have a database something like this:

Person Sales

Chris 50.00
Mike 55.00
Joe 75.00
Chris 99.00
Chris 77.00
Mike 36.00
Joe 80.00

I want to count how many times the person shows up and the total of sales. I have written a sql script to count but I can't get the sum to work the way I want to.

This is what I have so far:

SELECT Table1.Person, COUNT(Table1.Person) AS ['Total']
FROM Table1
GROUP BY Table1.Person
ORDER BY COUNT(Table1.Person) DESC;

How can I enter a column that would add the total sales amount for that person? In fact I wouldn't mind a fourth column that would give the average sale, i.e. Total_Sales / COUNT(Table1.Person)

Thx,

Zych

 

[PHV]

Something like this ?
SELECT Person, COUNT(*) AS HowManyTimes, SUM(Sales) AS Total, AVG(Sales) AS Average
FROM Table1
GROUP BY Person
ORDER BY 2 DESC

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Zych]

Thanks! I thought I did this but it gave me multiple lines. I tried it again (I am using Access for the moment) and it did it again but I then noticed it added another group by which was screwing it up. Once I removed that it worked like a charm.

Thanks again!

- Zych