CODEWITHSUNDEEP
mysql
Aeykash
Aeykash

Reputation: 135

Calculation inside the query

SELECT a.id, a.username, a.email, a.created, b.user_id, 
    SUM( (b.price >500) +100 ) + SUM( b.price ) AS gr, SUM( b.price ) AS pris,
     b.created, c.user_id, c.referrer_id, c.created, d.id, d.beginner, d.winner_id
FROM users a, accounts b, referrals c, product d
WHERE a.created
BETWEEN  '2013-05-01'
AND  '2013-05-31'
AND a.id = b.user_id
AND a.id = c.user_id
AND d.beginner !=  '1'
AND d.winner_id = a.id
GROUP BY c.referrer_id
ORDER BY  `pris` ASC 
LIMIT 0 , 3

I have formulated this query. Problem that I want to solve in this is that with 

SUM(( b.price >500) +100) + SUM( b.price ) AS gr

I want it to be calculating like if total price of one id is more than 500 in one month then it adds 100. I don't know exactly how should I count total price of one person in this query and add 100 if count is more than 500 in one month?

Upvotes: 0

Views: 67

Answers (3)

Tom Mac
Tom Mac

Reputation: 9853

Whoops. Just re-read your question.

So total price for one id:

sum(b.price)

Test when total price for one id is > 500 and add 100 if it is more than 500:

sum(b.price) + case when sum(b.price) >500 then 100 else 0 end as gr

Upvotes: 0

PSR
PSR

Reputation: 40318

You can use CASE statement here 

SUM( CASE WHEN b.price >500 THEN b.price+100 ELSE b.price END) as gr

Upvotes: 1

Ankur Trapasiya
Ankur Trapasiya

Reputation: 2200

Try this in your query.

SUM(if(b.price >500,100,0))

Upvotes: 0

Related Questions