CODEWITHSUNDEEP
mysql
user1648175
user1648175

Reputation: 47

mysql query using condition inside sum

I have a column (*Purchasetype*), userid in video table purchasetype is some how containg values 0,1, 2,3,4,.. etc. I want two sum these value order by userid.

For ex: sum ( purchasetype ) order by userid but I want like this

if purchasetype= 0  then its value is 0.99
if purchasetype =1 or 20  then its value is 3.99
if purchasetype = 3 or 13or 22  then its value is 9.99

so on. Below is complete list

0 ,17= 0.99
1,20=3.99
2=6.99
3,13,22=9.99
4,5,6,7,8,,10,11,12=0.00
14=19.99
15,23=39.99
16,24=59.99
18,21=01.99
19=02.99
else
19.99

i want to sum all the values of purchasetype with their replaced values (given above) order by userid

do we can put condition inside the sum() function of mysql; If its possible then please give me solution , may be this will solve my problem

Upvotes: 2

Views: 639

Answers (3)

William The Dev
William The Dev

Reputation: 505

Although this doesn't use SUM from MySQL query, the logic will get the job done

    $query = mysqli_query($link, "SELECT * FROM video");
      while($row = mysqli_fetch_assoc($query)){
         //Then set conditionals 
         if($row['purchase_type)==0 || $row['purchase_type)==17){
              $values[] = 0.99;
             //Then your mysqli_query here

           }
    elseif($row['purchase_type)==1 || $row['purchase_type)==20){
           $values[]= 3.99;           

           }
    elseif//Blah blah for all values
         }
//After exhausting all the rows, add the SUM
$sum = array_sum($values); //$sum will be equal to the addition of all the vlues of the //array $values

Upvotes: 0

valex
valex

Reputation: 24134

I think the best way is to create table ptPrices:

create table ptPrices (Purchasetype int, Price float);
insert into ptPrices values (0,0.99);
insert into ptPrices values (1,3.99);
....
insert into ptPrices values (19,2.99);

And then use this query:

select sum(isnull(ptPrices.Price,19.99)) from Table 
left join ptPrices 
    on Table.Purchasetype=ptPrices.Purchasetype;

Upvotes: 0

Taryn
Taryn

Reputation: 247610

You would use the aggregate function SUM() and CASE:

select 
  SUM(CASE purchaseType
        WHEN 0 or 17 THEN 0.99
        WHEN 1 or 20 THEN 3.99
        WHEN 3 or 13 or 22 THEN 9.99 
        WHEN 4 or 5 or 6 or 7 or 8 or 9 or 10 or 11 or 12 THEN 0
        WHEN 14 THEN 19.99
        WHEN 15 or 23 THEN 39.99
        WHEN 16 or 24 THEN 59.99
        WHEN 18 or 21 THEN 1.99
        WHEN 19 THEN 2.99
        ELSE 19.99 END) as Total
from yourTable

see SQL Fiddle with Demo

Upvotes: 4

Related Questions