mysql里面的选择运算,MySQL从多个列中选择并计算值

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

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.

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).

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

REQUEST_ID TOTAL_VALUE

10003 14.07

10004 20.75

...

...

Thank you in advance.

解决方案

Why do you use "where" clause?

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)