MySQL从多列中选择并计算值

I am trying to create a mysql select query of which calculates a value based on two other fields.

我正在尝试创建一个mysql选择查询,该查询基于另外两个字段计算值。

This is my query,

这是我的查询,

SELECT request_id, (
unit_cost * quantity
) AS claim_value
FROM  `xx_non_part_usage` 
WHERE request_id = request_id
GROUP BY request_id

The query above only brings back the total_value for one of the rows.

上面的查询仅返回其中一行的total_value。

For example - here is some sample data,

例如 - 这是一些示例数据,

ID      REQUEST_ID      QUANTITY        UNIT_VALUE
1       10001           2.0             3.00
2       10001           1.0             19.00
3       10003           0.5             18.00
4       10001           10.0            12.00
5       10003           0.75            6.76
6       10002           9.0             3.20
7       10001           0.10            13.80
8       10001           1.0             90.99
9       10004           6.75            3.00
10      10009           3.23            87.00

As you can see there are several rows of REQUEST_ID '10001'. What the query needs to do is REQUEST_ID * QUANTITY then group them so it only returns the final value price (adding all of the results from the multiply sum (REQUEST_ID * QUANTITY).

如您所见,有几行REQUEST_ID'10001'。查询需要做的是REQUEST_ID * QUANTITY然后对它们进行分组,使其仅返回最终价格(从乘法和(REQUEST_ID * QUANTITY)中添加所有结果)。

Here is an expected result of what I am hoping to get (different example on REQUEST_ID 10003,

这是我希望获得的预期结果(REQUEST_ID 10003上的不同示例,

REQUEST_ID      TOTAL_VALUE
10003           14.07
10004           20.75
...
...

Thank you in advance.

先感谢您。

2 个解决方案

#1

Why do you use "where" clause?

为什么使用“where”子句?

mysql> select * from test;
+------------+------------+----------+
| request_id | unit_value | quantity |
+------------+------------+----------+
|          1 |          3 |        2 |
|          1 |         19 |        1 |
|          2 |       6.76 |     0.75 |
|          2 |         18 |      0.5 |
+------------+------------+----------+
4 rows in set (0.00 sec)

mysql> SELECT request_id, sum(unit_value * quantity) as x from test group by request_id;
+------------+--------------------+
| request_id | x                  |
+------------+--------------------+
|          1 |                 25 |
|          2 | 14.070000171661377 |
+------------+--------------------+
2 rows in set (0.00 sec)

#2

ID      REQUEST_ID      CLAIM_VALUE
3       10003           9.00
5       10003           5.07

You want to use SUM

您想使用SUM

SELECT 
  request_id,
  SUM(unit_cost * quantity) AS claim_value
FROM  `xx_non_part_usage` 
WHERE request_id = request_id
GROUP BY request_id

You shouldn't be using GROUP BY unless you are using an aggregate function.

除非使用聚合函数,否则不应使用GROUP BY。

#1