mysql query using condition inside sum

Question

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

Taryn · Accepted Answer · 2012-09-12 11:30:55Z

4

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

answered Sep 12, 2012 at 11:30

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Taryn Over a year ago

@user1648175 glad it is working. if an answer helped you then be sure to mark it as accepted via the checkmark on the left. It will help future visitors as well.

valex · Accepted Answer · 2012-09-12 11:37:11Z

0

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;

answered Sep 12, 2012 at 11:37

valex

24.2k7 gold badges47 silver badges62 bronze badges

Comments

William The Dev · Accepted Answer · 2012-09-12 11:38:48Z

0

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

answered Sep 12, 2012 at 11:38

William The Dev

5051 gold badge6 silver badges16 bronze badges

Collectives™ on Stack Overflow

mysql query using condition inside sum

3 Answers 3

see SQL Fiddle with Demo

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

see SQL Fiddle with Demo

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related