Mysql 查询中 Sum 的平均值
我在创建一个给出总和平均值的查询时遇到一些问题。我在 stackoverflow 上读了一些例子,但无法做到。谁能帮助我理解如何做到这一点?这是我拥有的数据:
Transaction_x0020_Number Product_x0020_Code Sales_x0020_Value Date Cashier
000356 350 24.99 2010-06-04 131
000356 726 32.99 2010-06-04 131
000357 350 24.99 2010-06-04 131
000358 350 24.99 2010-06-04 131
000358 360 24.99 2010-06-04 131
000770 703 69.99 2010-06-04 130
000771 726 32.99 2010-06-04 130
000772 1126 5 2010-06-04 130
000773 482 32.99 2010-06-04 130
000774 600 32.99 2010-06-04 130
000775 350 24.99 2010-06-04 130
基本上我需要收银员的平均交易价值。我无法运行基本平均值,因为它将占用所有行,但每个事务可以有多行。最后我想要:
Cashier| Average|
131 | 44.31 |(Which comes from the sum divided by 3 transactions not 5 rows)
130 | 33.15 |
etc.
这是我必须对事务求和的查询,但不知道如何或在哪里包含 AVG 函数。
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'})
任何帮助表示赞赏。
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:
Transaction_x0020_Number Product_x0020_Code Sales_x0020_Value Date Cashier
000356 350 24.99 2010-06-04 131
000356 726 32.99 2010-06-04 131
000357 350 24.99 2010-06-04 131
000358 350 24.99 2010-06-04 131
000358 360 24.99 2010-06-04 131
000770 703 69.99 2010-06-04 130
000771 726 32.99 2010-06-04 130
000772 1126 5 2010-06-04 130
000773 482 32.99 2010-06-04 130
000774 600 32.99 2010-06-04 130
000775 350 24.99 2010-06-04 130
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.
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(1)