How to count & sort multiple columns in 1 MySQL table?

Question

Hi, I know how to count just one column in my table, but I want to count the following 14 columns and do the same for every record in my database, and then sort them based on whose name is top etc

Home Scorer 1, Home Scorer 2, Home Scorer 3, Home Scorer 4, Home Scorer 5, Home Scorer 6, Home Scorer 7, Away Scorer 1, Away Scorer 2, Away Scorer 3, Away Scorer 4, Away Scorer 5, Away Scorer 6, Away Scorer 7,

There are 200+ records some of which have scorers and some don't, I want to count every scorer name for every row and then display an overall total E.G "Dave Jones - 42, Alan X - 34" etc etc in a table, based on the entire table. I don't know if this is possible (I have done it for competition (a different column) before, but that's only 1), hopefully I have explained it in enough detail

I am using PHP to connect & get data from the MySQL database.

Thanks if anyone can help

Zack

Answer 1

What you want is possible, but your schema is set up in a way that make s it difficult and inefficient to do so.

However you could do it like so (I'll limit the fields for the example):

SELECT Player,COUNT(Player) Total FROM

(SELECT `Home Scorer 1` Player FROM Table

UNION ALL SELECT `Home Scorer 2` FROM Table

UNION ALL SELECT `Away Scorer 1` FROM Table

UNION ALL SELECT `Away Scorer 2` FROM Table) PlayerTotals

GROUP BY Player ORDER BY COUNT(Player) DESC,Player;

This will give you a record set with player names and their score. From here, you can use PHP to display in the format you wish ("Alan X - 34"), or you could use the SQL CONCAT function to do that - I'd do it in PHP.

If I were you, I'd look improving (and normalizing) your database schema. Don't have a field for each score of the game - have that has a separate table. As an example, consider this schema:

Game:

Id

Date

StartTime

EndTime

Players:

Id

Name

GamePlayers:

GameId

PlayerId

Scorers:

Id

GameId

PlayerId

ScoreTime

A schema like this would not only be more efficient and more flexible, but easier to work with:

SELECT P.Name,COUNT(S.PlayerId) Total FROM Scorers S RIGHT JOIN Players P ON S.PlayerId=P.Id

GROUP BY S.PlayerId ORDER BY COUNT(S.PlayerId) DESC,P.Name;

Much neater.