Given the following schema:
Code:
CREATE TABLE Classes (
class varchar(20),
type varchar(2),
country varchar(20),
numGuns int,
bore int,
displacement int
);
CREATE TABLE Battles (
name varchar(20),
date DATE
);
CREATE TABLE Outcomes (
ship varchar(20),
battle varchar(20),
result varchar(20)
, sunkBy varchar(20));
CREATE TABLE Ships (
name varchar(20),
class varchar(20),
launched int
);
I'm trying to for each class of ships that includes at least 2 ships, find the number of ships sunk by a ship of that class.
I have the following sql query:
Code:
WITH M AS(
SELECT class from Ships
GROUP BY class
HAVING COUNT(class) > 1
)
SELECT ships.class, COUNT(*)
FROM Outcomes, Ships, m
WHERE Outcomes.sunkby = Ships.name AND M.class = Ships.class
GROUP BY ships.class;
But it only returns info about classes who have sunk at least 1 ship. The where clause causes classes with no sunken ships to be ignored.
I would however like to list also the classes who haven't sunk any ships, and have them displayed as {'ClassName', 0}.
To abstract the question a bit, I'm looking for a way to return also rows that have count = 0, in a count/group by query.