Posted on 2008-10-19
I have two tables - Jobs and Users.
Jobs contains all the records and $ values of each sale.
Users contains the reference to User name and UserID that I use to associate the right user name with the record in Jobs (which only keeps the userID).
I hope to have a query that SUMS and AVGs each User's sales figures, but I can't get the SQL right. My current attempt includes a bunch of Sort Bys which it appears I have to have present if I'm using SUM or AVG.
Here is my current SQL and it's only giving me the first record, with no sum or average other than just showing that first Job's $ amount:
SELECT SUM(Jobs.AcceptedPrice) AS TOTAL, AVG(Jobs.AcceptedPrice) AS AVERAGE, Jobs.JobID, Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName
FROM Jobs RIGHT JOIN Users ON Jobs.EstimatorAssigned=Users.UserID
WHERE Users.UserType=2 GROUP BY Jobs.EstimatorAssigned, Jobs.EarliestEstimateDateTime, Users.UserID, Users.UserFirstName, Jobs.JobID
I was hoping my final looped array (a DREAMWEAVER recordset so I have add other functions) would show the first user name, his total, and his average, then move to the next unique username in the list and show the same thing.
I'm keeping the date in there so I can run search queries that filter this recordset by date range (hence the need for a standard DW recordset)
What am I doing wrong here?