egolive egolive - 4 months ago 8
SQL Question

Count most record from tow columns

Hello i have a table (Spiel), where i have two Columns (SpielerIDForderer, SpielerIDGefordert). From this two col i want the record which occurs most
and count it together. So if the value/id 2 comes two times at SpielerIDForderer and seven times at SpielerIDGefordert, it should return 9.

This is my table

+-------------------+--------------------+
| SpielerIDForderer | SpielerIDGefordert |
+-------------------+--------------------+
| 5 | 2 |
| 3 | 2 |
| 3 | 2 |
| 3 | 8 |
| 6 | 2 |
| 3 | 2 |
| 3 | 2 |
| 2 | 3 |
| 2 | 2 |
+-------------------+--------------------+


And this is my SQL which is not working correct:

SELECT SUM(dum.tab) AS total FROM (
SELECT COUNT(SpielerIDForderer) AS tab FROM pddb.Spiel AS b
UNION ALL
SELECT COUNT(SpielerIDGefordert) AS tab FROM pddb.Spiel AS a WHERE SpielerIDGefordert=SpielerIDForderer
) AS dum


My expected result in this case is 9

Answer
SELECT value, count(*) AS total FROM (
    SELECT SpielerIDForderer AS value FROM pddb.Spiel AS b
    UNION ALL
    SELECT SpielerIDGefordert AS value FROM pddb.Spiel AS a
) AS dum
group by value
order by total desc
limit 1
Comments