mysql怎么将数据乘,MySQL查询-'CAST''CONCAT'-如何将几行数据乘以一定数量，并在£的新列中显示它们的总计？...

What's the best way to query a total price?

I want to multiply several rows of data by a certain amount and display their individual totals in a new column in £'s in my database?

What syntax do I need?

Edit:

I have loads of customers. Some have only one order, some multiple orders. I want to start charging them £1.50 per order, therefore, I need x(times) the order amount by £1.50 and display it in a new column in £. E.g customers with 4 order would need to be x £1.50 which would display £6.00 in column 3 and so on... 1.st column is name, second column is order amount. 3rd column needs to be total price. Hope that makes sense

Update from comments:

It's counted the orders, however it's returning BLOB values in the 3rd column where I want to display £ values for the * calculation of orders:

SELECT CONCAT_WS(" "

, c.customer_title

, c.customer_fname

, c.customer_sname

) AS Customer

, COUNT(O.order_name) AS Ordertotal

, concat('£' * 1.5) TotalPrice

FROM Order O, Friend F, Customer C, FriendOrder

WHERE C.customer_id = F.Customer_id

AND F.Friend_id = FriendOrder.friend_id

AND O.order_id = FriendOrder.order_id

GROUP BY Customer

ORDER BY C.customer_sname, C.customer_fname

解决方案

You shouldn't add the answer to the original question, since this makes finding out the question confusing.

It looks like everything was answered except the blob part -- here is the final result:

SELECT

CONCAT_WS(

" ",

c.customer_title,

c.customer_fname,

c.customer_sname

) AS Customer,

COUNT(*) AS Ordertotal,

CONCAT('£', cast(count(*) * 1.5 as char)) AS TotalPrice

FROM Order O

INNER JOIN FriendOrder fo

ON O.order_id = fo.order_id

INNER JOIN Friend F

ON fo.friend_id = F.Friend_id

INNER JOIN Customer C

ON F.Customer_id = C.customer_id

GROUP BY Customer

ORDER BY C.customer_sname, C.customer_fname

To avoid the 'blobs', cast to char since you are creating a display string. Here is the snippet from the query:

cast(count(*) * 1.5 as char)