Mantas Mantas - 6 months ago 8
PHP Question

How to count mutiple columns occurrencies in whole table

I have 5 columns that has data which are ids from other table Database table here

how do I count how many times each number appears in whole table, in all records.
I want to count paslauga1, paslauga2, paslauga3, paslauga4, paslauga5 data occurrencies.
For example there is 2 records that has 4 times 1 id 2 times 2 id 1 time id 3
So I want it to output

1 has appeared 4 times
2 has appeared 1 times
3 has appeared 2 times


and if more ids would be there 4,5,6 etc it would display them too.

I need to count this so I can use it on php code, maybe theres easier way to do so in php?

Answer
SELECT paslauga, count(paslauga)
FROM (
       SELECT paslauga1 as paslauga FROM yourTable
       UNION ALL
       SELECT paslauga2 as paslauga FROM yourTable
       UNION ALL
       SELECT paslauga3 as paslauga FROM yourTable
       UNION ALL
       SELECT paslauga4 as paslauga FROM yourTable
       UNION ALL
       SELECT paslauga5 as paslauga FROM yourTable
     ) T
GROUP BY paslauga
Comments