CODEWITHSUNDEEP
sqlsql-serverjoinselectleft-join
Ploughansen
Ploughansen

Reputation: 313

Count of column from joined table but include zeroes (can't get LEFT JOIN to work)

I have to tables. One with Users (called Actor) and one with Orders (called Ord).

Each order can be assigned to one user, but not all orders have users assigned.

I want to make a select statement that returns all my users and show how many orders are assigned to each one. Including those users that have no orders assigned.

Pretty simple, but for some reason I can't get it to work.

This is what I've got so far:

SELECT Usr, COUNT(Ord.OrdNo)
FROM Actor
    LEFT JOIN Ord on Actor.EmpNo = Ord.Rsp
WHERE Actor.Gr2 <> 9
    AND Actor.R1 = 10
    AND Ord.OrdTp = 6
    AND Ord.OrdPrSt2 != 0x10
GROUP BY Actor.Usr

This just returns the users that have orders assigned to them, but I want all users returned, just with a zero if they have no orders assigned.

Please disregard the WHERE clauses (I havent't included the columns/values in the sample data below). Despite these I should still get around 10 users returned when running the query on my live data.

Sample data

Table: Actor

Usr      EmpNo
----------------
ben      123
jane     124
jack     125
tom      126

Table: Orders

OrdNo    Rsp
----------------
555551   123
555552   123
555553   124
555554   126

This is what I want returned:

Usr      Count
----------------
123      2
124      1
125      0
126      1

But instead I get this:

Usr      Count
----------------
123      2
124      1
126      1

So what am I missing?

Upvotes: 0

Views: 48

Answers (2)

bindu madhavi
bindu madhavi

Reputation: 64

without the where condition i am getting the desired results. here is the code i ran:

enter image description here

select a.empno, count(ordrno)
from actor a left join orders b on a.empno = b.rsp
group by a.empno

Upvotes: 0

jarlh
jarlh

Reputation: 44786

Move the Ord conditions from WHERE to ON to get true LEFT JOIN result:

SELECT Usr, COUNT(Ord.OrdNo)
FROM Actor
    LEFT JOIN Ord on Actor.EmpNo = Ord.Rsp
        AND Ord.OrdTp = 6
        AND Ord.OrdPrSt2 != 0x10
WHERE Actor.Gr2 <> 9
    AND Actor.R1 = 10
GROUP BY Actor.Usr

Upvotes: 1

Related Questions