Sikarjan Sikarjan - 10 months ago 52
MySQL Question

How to count multiple rows with mysqli?

I am facing a problem I am not capable to solve on my own. There a several questions out that regarding counting but I did no see one where the counting is done like I would need it. In my table I have three slots that can be booked by a Person, represented by their ID, like this:

ID | Slot1 | Slot2 | Slot3
1 45 53
2 1 27 6
3 53
4 6 45
5 15 53

It is possible that slots are free but it is not possible that an ID blocks to slots.

Now I would like to count how often each ID used on of the three slots. The result would look like this for the table above:

ID Count
1 1
6 2
15 1
27 1
45 2
53 3

Is that possible with one mysql statement or do I need to GROUP BY for each slot and add the slots up later in my script?

If it is possible to do the counting in mysql over all three slots, would it also be possible to join the result with a second table that holds the names to the IDs?

Answer Source

You want a union all and aggregation:

select slot, count(*)
from ((select slot1 as slot from t
      ) union all
      (select slot2 as slot from t
      ) union all
      (select slot3 as slot from t
     ) s
where slot is not null
group by slot