CODEWITHSUNDEEP
mysqlif-statementgroup-byconditional-statements
Nitheesh Unnikrishnan
Nitheesh Unnikrishnan

Reputation: 300

mysql if condition add value else add value1

I'am trying to get total full time and total half time by user, Timing is stored in single column, Integer value of timing should come in full time as sum(timing) and floating value in half time but in count

id user_id timing
1   2       1
2   2       2.5
3   1       1.5
4   1       1
5   3       3
6   2       2.5

I need the result as

 user_id  fulltime halftime
   1        2         1
   2        5         2
   3        3         0

Upvotes: 0

Views: 118

Answers (2)

Amit Shah
Amit Shah

Reputation: 1380

This query should help you. please try it on your data

SELECT  user_id, 
        sum(if(ceil(timing)>timing,0,timing)) as fulltime,
        sum(if(ceil(timing)>timing,timing,0)) as halftime 
FROM    rest 
GROUP BY user_id

Thanks Amit

Upvotes: 0

shmosel
shmosel

Reputation: 50726

SELECT user_id
     , SUM(FLOOR(timing)) AS fulltime
     , SUM((timing % 1) * 2) AS halftime
  FROM table
 GROUP BY user_id;

Upvotes: 3

Related Questions