MySQL Select count, group by distinct - two columns-CodePudding

I want to get on Mysql :

Count number of rows where column done is < 2
Get the runner id
but group if runnerA and runnerB are same person.

So I want to know the number of rows where each runner is present, even if he is in runnerA or runnerB.

An exemple :

My datas :

id	id_runnerA	id_runnerB	done
1	12	5	1
2	20	12	1
3	12	1	3

The result should be :

id_runner	count
12	2
20	1
5	1

Thank you

CodePudding user response：

Looks like it might be

SELECT
  id_runner, count(*)
FROM
  (
    SELECT id_runnera as id_runner FROM t WHERE done < 2
    UNION ALL
    SELECT id_runnerb FROM t WHERE done < 2
  ) x
GROUP BY
  id_runner

But it'd be helpful to see some more test data