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;

Could you help us out and let us know what exactly isn't working correctly? — Randy Minder
– Randy Minder, Commented Nov 9, 2015 at 14:02
Sorry, the output is 0 for each one, and I know it should be; Ewe = 3, Lamb = 5, and Ram = 1 — user3599441
– user3599441, Commented Nov 9, 2015 at 14:04
Don't use inner join if you want to count tabel data in each of those tables. You will get the same count of rows in every table because you create a hard connection between every table in inner join. — Marcus Höglund
– Marcus Höglund, Commented Nov 9, 2015 at 14:05

Richard · Accepted Answer · 2015-11-09 14:06:55Z

3

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

answered Nov 9, 2015 at 14:06

Richard

110k21 gold badges214 silver badges279 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

aleroot · Accepted Answer · 2015-11-09 14:10:15Z

I think that the problem here is that you don't need an INNER JOIN but an OUTER JOIN ...

SELECT COUNT(CASE WHEN e.EweID IN NOT NULL THEN e.EweID ELSE 0 END) AS 'Ewe', COUNT(r.RamID) AS 'Ram', COUNT(l.LambID) AS 'Lamb' 
FROM Sheep s 
    LEFT OUTER JOIN Ewe e ON s.SheepID = e.EweID 
    LEFT OUTER JOIN Ram r ON s.SheepID = r.RamID 
    LEFT OUTER JOIN Lamb l ON s.SheepID = l.LambID 
WHERE s.FarmerID = '123'

Take a look even at the case statement that I've added inside the first count(Ewe), to see a way to handle nulls in the count .

The Left Outer Join logical operator returns each row that satisfies the join of the first (top) input with the second (bottom) input. It also returns any rows from the first input that had no matching rows in the second input. The nonmatching rows in the second input are returned as null values. If no join predicate exists in the Argument column, each row is a matching row.

jarlh · Accepted Answer · 2015-11-09 14:08:10Z

0

Use correlated sub-selects to do the counting:

SELECT (select COUNT(*) from Ewe e where s.SheepID = e.EweID) AS 'Ewe',
       (select COUNT(*) from Ram r where s.SheepID = r.RamID) AS 'Ram',
       (select COUNT(*) from Lamb l where s.SheepID = l.LambID) AS 'Lamb'
FROM Sheep s 

WHERE s.FarmerID = '123'

And you can also simply remove the WHERE clause to get all farms' counts.

answered Nov 9, 2015 at 14:08

jarlh

44.9k8 gold badges52 silver badges68 bronze badges

Comments

Jesse Petronio · Accepted Answer · 2015-11-09 14:09:52Z

0

DECLARE @Count1 INT;

SELECT  @Count1 = COUNT(*)
FROM    dbo.Ewe;

DECLARE @Count2 INT;

SELECT  @Count2 = COUNT(*)
FROM    dbo.Ram;

DECLARE @Count3 INT;

SELECT  @Count3 = COUNT(*)
FROM    dbo.Lamb;

SELECT  @Count1 AS 'Ewe' ,
        @Count2 AS 'Ram' , 
        @Count3 AS 'Lamb'

answered Nov 9, 2015 at 14:09

Jesse Petronio

7235 silver badges12 bronze badges

Collectives™ on Stack Overflow

Using the COUNT with multiple tables

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related