CODEWITHSUNDEEP
mysqlsqlmath
lewiswalsh
lewiswalsh

Reputation: 65

Complex arithmetic in MySQL query

I have a table as follows:

product | quantity | price | gift | giftprice
--------|----------|-------|------|----------
1       | 2        | 9.99  | 0    | 4.99
2       | 3        | 3.50  | 1    | 2.25
3       | 1        | 4.75  | 1    | 1.50

What I'd like to have an SQL query that will give me a figure that gives me the sum of all the records with quantity multiplied by price with the giftprice being added to the price before multiplication only if the 'gift' field is set to 1.

Pseudocode

foreach(record){
   if(gift == 1){ linetotal = (price + giftprice) * quantity; }
   else { linetotal = price * quantity; }
   total = total + linetotal;
}

Upvotes: 0

Views: 4967

Answers (2)

David Tang
David Tang

Reputation: 93664

You can just do:

SELECT product, (price + gift * giftprice) * quantity AS total
FROM theTable

Since nothing will be added if gift = 0.

Upvotes: 9

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

SELECT SUM( (price + giftprice*gift) * quantity)
       AS total
FROM yourTable
;

Upvotes: 3

Related Questions