Q:

How to select both count and another value from two different tables?

In oracle I have a table called classes which lists out the classes of ships and another table called ships which lists out the names of the ships for these classes. Now I need to find out for each class that has at least 3 ships, how many times has the ships of those classes sunk. To do that I have an outcomes tables which tells me the ship name as well as the result. To do this I've first created a view:
CREATE VIEW classes_with_3_ships AS
    SELECT c.class
    FROM Classes c FULL OUTER JOIN Ships s ON s.class = c.class
    GROUP BY c.class
    Having COUNT(s.class) >= 3;

And this lists out all the classes that have at least 3 ships. Now I'm trying to use this view to count the amount of times the result in outcomes has been 'sunk'. 
SELECT c.class, Count(result)
FROM Outcomes o, classes_with_3_ships c
WHERE o.RESULT = 'sunk';

And this doesn't work because apparently you can't select both c.class and the count. So how can I make this work? 
Some sample data
For Classes
Insert Into Classes Values ('Renown','bc','Gt. Britain',6,15,32000);

Insert Into Classes Values ('Kongo','bc','Japan',8,14,32000);

Insert Into Classes Values ('Revenge','bb','Gt. Britain',8,15,29000);

For Ships:
Insert Into Ships Values ('Haruna','Kongo',1915);

Insert Into Ships Values ('Hiei','Kongo',1914);

Insert Into Ships Values ('Kongo','Kongo',1913);

Insert Into Ships Values ('Ramilles','Revenge',1917);

Insert Into Ships Values ('Resolution','Revenge',1916);

Insert Into Ships Values ('Revenge','Revenge',1916);

Insert Into Ships Values ('Royal Oak','Revenge',1916);

So the result of the view should be
Revenge
Kongo

And the result of the query should be
Class        sunk_ships
Revenge          0
Kongo            1

And here are the tables
Create Table Classes (
    class Varchar(40),
    type Char (2),
    country Varchar(15),
    numGuns Int,
    bore Int,
    displacement Int
);
Create Table Ships (
    name Varchar(40),
    class Varchar(40),
    launched Int
);
Create Table Outcomes(
    ship Varchar(40),
    battle Varchar(40),
    result Char(7)
);

A:

You could try it without the view and use a subquery instead.
Try this query:
SELECT
s.class,
SUM(CASE o.result WHEN 'sunk' THEN 1 ELSE 0 END) AS sunk_ships
FROM ships s
LEFT JOIN classes c ON s.class = c.class
LEFT JOIN outcomes o ON s.name = o.ship
WHERE c.class IN (
    SELECT
    s.class
    FROM ships s
    GROUP BY s.class
  HAVING COUNT(*) >= 3
)
GROUP BY s.class;

The inner query finds the classes that have at least 3 ships. This is used to filter the outer query, which joins the three tables together.
The view is not needed.
One thing to be careful of is the case of the word "sunk". If it is always lowercase, that's fine. Otherwise you might want to enclose the o.result in a LOWER function.
Does this give you the result you need?

