CODEWITHSUNDEEP
phpmysql
user2282217
user2282217

Reputation: 91

How to count result from two columns in mysql

I have a prediction table including homescore and awayscore. I want to output all distinct predictions and how many instances of each prediction exists.

matchid    homescore    awayscore
 1         1            2
 1         1            0
 1         9            3
 1         2            0
 1         1            2
 1         1            0
 2         3            2
 2         2            2
...

I want this to output a table like this for matchid 1:

result    predictions
1-2       2           
1-0       2
9-3       1
2-0       1

Upvotes: 1

Views: 54

Answers (1)

Mark Baker
Mark Baker

Reputation: 212452

SELECT 
    CONCAT(homescore, '-', awayscore) as result,
    COUNT(*) as predictions
FROM table
WHERE matched = 1
GROUP BY CONCAT(homescore, '-', awayscore);

Upvotes: 2

Related Questions