Average of a Sum in Mysql query
- by chupeman
I am having some problems creating a query that gives me the average of a sum. I read a few examples here in stackoverflow and couldn't do it. Can anyone help me to understand how to do this please? This is the data I have:
Basically I need the average transaction value by cashier. I can't run a basic avg because it will take all rows but each transaction can have multiple rows. At the end I want to have:
Cashier| Average|
131 | 44.31 |(Which comes from the sum divided by 3 transactions not 5 rows)
130 | 33.15 |
etc.
This is the query I have to SUM the transactions but don't know how or where to include the AVG function.
SELECT `products`.`Transaction_x0020_Number`,
Sum(`products`.`Sales_x0020_Value`) AS `SUM of Sales_x0020_Value`,
`products`.`Cashier`
FROM `products`
GROUP BY `products`.`Transaction_x0020_Number`, `products`.`Date`, `products`.`Cashier`
HAVING (`products`.`Date` ={d'2010-06-04'})
Any help is appreciated.