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

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.

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...

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

MySQL - MyISAM - 5.6.48-88.0

CodePudding user response：

Your first sentence 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