Mysql Query - 'cast' ' Concat' - How To Multiply Several Rows Of Data By A Certain Amount And Display Their Individual Totals In A New Column In £'s?

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?

Solution 1:

It is a best practice to separate tasks, leaving computation to the SQL programming, and presentation to whatever programming language you use for the front end.

So your SQL should use it's native * operator. Your query might look like:

SELECT`column_1` * `column_2`as`product`;

this would return the product of two columns in a column named 'product'.

The £ sign is formatting. You should leave that to whatever architecture you have written for presenting the information. (PHP or java for example)

Solution 2:

Applying a lot of imagination because of the lack of description of your data and fields, this should do the trick:

select c.name, count(*) orderAmount, concat('£', count(*) * 1.5) totalPrice
from customers c
join orders o on c.customerId = o.customerId
groupby c.customerId, c.name

Solution 3:

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 FROMOrder O
INNER JOIN FriendOrder fo
ON O.order_id = fo.order_id
INNER JOINFriend F
ON fo.friend_id = F.Friend_id
INNER JOIN Customer C
ON F.Customer_id = C.customer_id
GROUPBY Customer 
ORDERBY 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)