MySQL multiple rows, grouping multiple values

Question

I really tried to do this by myself but seems that there is too complex query for a newbie like me.

on dgpl_se_bets1x2 table i have values for:

id, id_mat, prev, note, datetime

what is important of these:

id_mat = match id

prev = bet placed by user, values are 1, x or 2.

I want to print statistics about made bets, here is few lines for example:

+--+------+----+----+-------------------+
|id|id_mat|prev|note|datetime           |
+--+------+----+----+-------------------+
|6 |442   |2   |1   |2014-01-03 11:04:08|
+--+------+----+----+-------------------+
|5 |442   |1   |1   |2014-01-03 11:04:08|
+--+------+----+----+-------------------+
|5 |449   |1   |1   |2014-01-02 16:40:28|
+--+------+----+----+-------------------+
|6 |449   |1   |0   |2014-01-02 16:40:28|
+--+------+----+----+-------------------+
|7 |442   |2   |0   |2014-01-03 11:04:08|
+--+------+----+----+-------------------+
|8 |442   |2   |1   |2014-01-03 11:04:08|
+--+------+----+----+-------------------+
|7 |636   |2   |0   |2014-01-03 15:46:34|
+--+------+----+----+-------------------+

So there are matches 5,6,7 and 8. And from that data I want to print out something like this:

+------------+-+-+-+
|Match Number|1|x|2|
+------------+-+-+-+
|5           |2|0|0|
+------------+-+-+-+
|6           |1|0|1|
+------------+-+-+-+
|7           |0|0|2|
+------------+-+-+-+
|8           |0|0|1|
+------------+-+-+-+

Or even better, with percentages:

+------------+---+-+---+
|Match Number|1  |x|2  |
+------------+---+-+---+
|5           |100|0|0  |
+------------+---+-+---+
|6           |50 |0|50 |
+------------+---+-+---+
|7           |0  |0|100|
+------------+---+-+---+
|8           |0  |0|100|
+------------+---+-+---+

Patrick Q · Accepted Answer · 2014-01-15 21:02:33Z

2

Give this a try

SELECT id_mat,
    (SUM(prev='1')/COUNT(*))*100 AS prev1,
    (SUM(prev='x')/COUNT(*))*100 AS prevX,
    (SUM(prev='2')/COUNT(*))*100 AS prev2
FROM dgpl_se_bets1x2
GROUP BY id_mat;

answered Jan 15, 2014 at 21:02

Patrick Q

6,3942 gold badges28 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yuha Over a year ago

This is exactly what I was looking for. Wasnt so hard that I thought it would be. Thank you very much Patrick :)

Collectives™ on Stack Overflow

MySQL multiple rows, grouping multiple values

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related