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

Question

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.

I tried with that.. But not getting it correctly.. Cn u pls tell me the exact query for the above scenario — user3615185, Apr 28 '15 at 14:20
Show us sample table data, your query (the one that doesn't work properly), and expected result! — jarlh, Apr 28 '15 at 14:21
SAmple Table A Code name A pal B john C APril Table B Code Value A 10 A 20 B 30 SAMple Out put: Code NoofEntries A 2 B 1 C 0 — user3615185, Apr 28 '15 at 14:33
My query: select a.code,a.name,count(b.code) from A a, B b where a.code=b.code group by a.code, a.name; — user3615185, Apr 28 '15 at 14:35

jarlh · Accepted Answer · 2022-03-14T16:15:34.083

2

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.

edited Mar 14 '22 at 16:15

answered Apr 28 '15 at 14:58

jarlh

42,561
8
45
63

score 1 · Answer 2 · answered Apr 28 '15 at 14:46

1

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

answered Apr 28 '15 at 14:46

Giorgi Nakeuri

35,155
8
47
75

This is perfect solution – Chiragkumar Thakar Apr 28 '15 at 14:53

Chiragkumar Thakar · Answer 3 · 2015-04-28T15:04:44.420

0

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

edited Apr 28 '15 at 15:04

answered Apr 28 '15 at 14:52

Chiragkumar Thakar

3,616
5
37
49

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

3 Answers3