GROUP_CONCAT row if values

Question

I need to count records from a table but I must treat values with pId 55 and 61 as one only if the user_id is the same.

id     pId  user_id

6126    55  127742
6128    55  127584
6132    58  128788
6134    55  54445
6136    55  127897
6139    61  127584



select id, pId, user_id, count(1),
    from table
    where date >= '2017-01-19 00:00:00' AND date < '2017-01-20 00:00:00'
    group by user_id
    ;

Did not quite understand. From your sample data, what should be the final result? And what does your request have to do with `group_concat`? — Jorge Campos, Jan 27 '17 at 11:37
I want to count the rows. Final result should be 9 rows not 10 because row with id `6128` has the same `user_id` as row `6139` and the `pId`s are 55 and 61. I just have to combine 55 and 61 when the same user_id is present. Edited sample data. — Ciprian, Jan 27 '17 at 11:42
See http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query — Strawberry, Jan 27 '17 at 11:59

score 0 · Answer 1 · answered Jan 27 '17 at 11:48

Create another table where 61 is replaced by 55. Now calculate whatever you want to calculate.

select a.user_id, count(distinct a.pid_user_id)
    from (select id, 
     concat(if(pid = 61, 55, pid) , '_', user_id) as pid_user_id, 
     user_id
     from table
     where date >= '2017-01-19 00:00:00' AND date < '2017-01-20 00:00:00') as a
group by a.user_id;

GROUP_CONCAT row if values

1 Answers1