Using the COUNT with multiple tables

Question

I want to count the number of different sheep, and I want it in one table.

Like this;

Ewes | Rams | Lambs
 8   |   5  |  12

The query I try is this, but it doesn't work;

SELECT COUNT(e.EweID) AS 'Ewe', COUNT(r.RamID) AS 'Ram', COUNT(l.LambID) AS 'Lamb' 
FROM Sheep s 
    INNER JOIN Ewe e ON s.SheepID = e.EweID 
    INNER JOIN Ram r ON s.SheepID = r.RamID 
    INNER JOIN Lamb l ON s.SheepID = l.LambID 
WHERE s.FarmerID = '123'

I don't get what I'm doing wrong, this is my database ERD;

Answer 1

I don't think you need a FROM here at all:

select
  (select count(*) from Ram where Famerid = 123) as RamCount,
  (select count(*) from Ewe where Famerid = 123) as Count,
  (select count(*) from Lamb where Famerid = 123) as LambCount

(There is no relationship between the rows you are counting, do don't try and create one. Instead count each separately, wrapping it all in an outer select keeps everything in a single result row.)