CODEWITHSUNDEEP
sqloracle-databaseleft-joinnvl
user3615185
user3615185

Reputation: 55

display Count of one column from another table even when the count is zero

I have two tables A and B. In Table A (Oracle sql), an unique column(not primary key) code may have some records in table B.

Example:

Code "A" has 3 entries, Code "B" has 2 entries and code "C" has 0 entries in table B. I want the query to display the code and its count of records in Table B. 

A 3
B 2
C 0,

But i am not getting the code with zero records in table B, i.e C 0.

Please anyone can help me with the query.

Upvotes: 0

Views: 2822

Answers (3)

jarlh
jarlh

Reputation: 44805

GROUP BY with LEFT JOIN solution:

select a.code,
       a.name,
       count(b.code)
from A a
  LEFT JOIN B b ON a.code = b.code
group by a.code, a.name

Correlated sub-query solution:

select a.code,
       a.name,
       (select count(*) from B b where a.code = b.code)
from A a

Perhaps you need to do SELECT DISTINCT here.

Upvotes: 2

Chiragkumar Thakar
Chiragkumar Thakar

Reputation: 3726

It is quite easy, you just need to Take column base on you want count as I did "A.code" and don't forget to Group by that column, and use COUNT(). Check the below solution 

   select A.code, Count(B.code) AS Count 
    from A
    left join B on A.code = b.code
    group by A.code

Upvotes: 0

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

You are doing something incorrectly. This works for me:

select A.code, Count(B.code) from A
left join B on A.code = b.code
group by A.code

Fiddle: http://sqlfiddle.com/#!4/f13e1/2

Upvotes: 1

Related Questions