Question: I need to transfer data from karma mod in phpbb to karma in smf.

Phpbb has a table `phpbb_karma` which is basically a karma log. Important fields we need to focus on are `user_id` (user which got the karma) and `karma_action` (positive or negative karma, shown as + or -).

I would need a query to count all the pluses and minuses and then copy the given results for each unique `user_id` into `smf_members` table under appropriate `id_member` (`user_id` field in smf) and `karma_good` (all the pluses) and `karma_bad` (all the minuses).

I think it can be done but I am not SQL expert.

Answer: 
```
update smf_members a, 
    (select user_id,
    sum(if(karma_action='+',1,0)) karma_good,
    sum(if(karma_action='-',1,0)) karma_bad
    from phpbb_karma
    group by user_id) b
set a.karma_good=b.karma_good, a.karma_bad = b.karma_bad
where a.id_member = b.user_id;

```
```
How about something like this:

    SELECT DISTINCT user_id, (SELECT COUNT(1) as points
    FROM phpbb_karma
    WHERE action = '+' and k.user_id = user_id), 
    (SELECT COUNT(1) as points
    FROM phpbb_karma
    WHERE action = '-' and k.user_id = user_id)
FROM
    phpbb_karma k

```

Not the most optimized one but given this is an one-off operation then it shouldnt matter much.