SQL count how many rows have the same value and do the sum on condition

Question

I have a question for you today. I have this table

oddId	risk	resultCode	finalResult
1	6.66667	2	1
2	7.14286	2	1
3	8.33333	2	1
4	8.33333	2	2
5	10	2	1
6	10	2	2
7	10	2	2
8	10	2	2
9	11.1111	1	2
10	11.1111	2	2
11	12.5	2	1
12	12.5	2	2
13	12.5	1	1
14	12.5	2	1
15	12.5	2	1
16	12.5	1	2
17	12.5	1	1
18	12.5	1	2
19	12.5	2	1
20	14.2857	2	2
21	14.2857	1	2
22	14.2857	2	2
23	16.6667	2	2
24	16.6667	1	2
25	16.6667	1	1

For this job I need

1. group the rows by "risk"
2. count how many rows have the same "risk" value
3. Count how many rows have the same value in "resultCode" and "finalResult"

---

With

SELECT  `risk`, `resultCode`, `finalResult`, 
        ( SELECT  CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
            FROM  matches
            WHERE  `resultCode` = t.`finalResult`
              AND  `oddId` = t.`oddId`
        ) equal
    FROM  matches t
    WHERE  `finalResult` IS NOT NULL
    ORDER BY  `t`.`risk` ASC

i can get "equal" column.

risk	resultCode	finalResult	equal
6.66667	2	1	0
7.14286	2	1	0
8.33333	2	1	0
8.33333	2	2	1
10	2	1	0
10	2	2	1
10	2	2	1
10	2	2	1
11.1111	1	2	0
11.1111	2	2	1
12.5	2	1	0
12.5	2	2	1
12.5	1	1	1
12.5	2	1	0
12.5	2	1	0
12.5	1	2	0
12.5	1	1	1
12.5	1	2	0
12.5	2	1	0
14.2857	2	2	1
14.2857	1	2	0
14.2857	2	2	1
16.6667	2	2	1
16.6667	1	2	0
16.6667	1	1	1

with

SELECT  `risk`, COUNT(`risk`) as total
    FROM  `matches`
    WHERE  `finalResult` IS NOT NULL
    GROUP BY  `risk`
    ORDER BY  `risk` DESC

I can get "total" column.

---

Now I would also like to have a column where I group by "risk" and sum the "equal" columns (into "corrects"), to have...

risk	corrects	total
6.66667	0	1
7.14286	0	1
8.33333	1	2
10	3	4
11.1111	1	2
12.5	3	9
14.2857	2	3
16.6667	2	3

but I don't know how to do... Can anyone help me please? Thank you very much

MySQL - MyISAM - 5.6.48-88.0

Answer 1

Your first sql is too confusing, so you don’t see the answer clearly

SELECT
    `risk`,
    COUNT( `risk` ) AS total,
    sum(case when resultCode = finalResult then 1 else 0 end) corrects
FROM
    `matches` 
WHERE
    `finalResult` IS NOT NULL 
GROUP BY
    `risk` 
ORDER BY
    `risk` DESC