Below is a solution which accounts the sunken ships correctly. This solution has an insignificant mistake; we suggest you to find it by yourself. In case of difficulty, return to solution 3.12.3.
SELECT class, SUM(r) sunks FROM (SELECT name, class, CASE WHEN result = 'sunk' THEN 1 ELSE 0 END r FROM Ships AS s LEFT JOIN Outcomes AS o ON o.ship = s.name UNION SELECT ship, class, CASE WHEN result = 'sunk' THEN 1 ELSE 0 END r FROM Classes c JOIN (SELECT * FROM Outcomes WHERE NOT Ship IN (SELECT name FROM Ships) ) AS ot ON ot.ship = c.class ) AS b GROUP BY class;
To return to discussion of exercise #56
To solve a problem on SQL-EX.RU
go
Console
