0

I have a table task 

select 
   sts_id, 
   count(*) mycount 
from 
   task
where 
sts_id in (1,  8,  39)
group by sts_id;

output :

   sts_id count
       1      1
       8      1
       39     1

I have one more temp table with one column sts_id which looks like this

      sts_id 
       1 
       8
       39
      40
      41.

I am trying for a left join for both the tables

select 
   in_list.sts_id, 
   count(*) mycount 
from 
   task
left outer join
   in_list
  on task.sts_id = in_list.sts_id 
group by sts_id;

to get ab o/p like

1 1
8 1
39 1
40 0
41 0..

I am getting an error of column ambiguously defined.

user2672165
  • 2,986
  • 19
  • 27
Soom Satyam
  • 25
  • 1
  • 1
  • 7
  • The error your getting is because the group by needs to have the table alias as well `in_list.sts_ID` However, _to get your desired results_ you need to **make your left join a right join** as the in_list has more values than task... based on your sample data provided. and `coalesce(count(task.Sys_ID),0) as myCount` in the select. – xQbert Sep 09 '16 at 15:37

4 Answers4

2

You are using left join the wrong way (on the left it must be the table with all the rows you want to show). Count (task.sts_id) to get 0 on rows without ocurrences on that table

select 
   in_list.sts_id, 
   count(task.sts_id) mycount 
from 
   in_list
left outer join
   task
  on in_list.sts_id = task.sts_id 
 AND task.sts_id in (1, 8, 39) -- Thanks Matt.
group by in_list.sts_id;
vercelli
  • 4,717
  • 2
  • 13
  • 15
  • @SoomSatyam try my second approach. I changed your count(*) – vercelli Sep 09 '16 at 15:18
  • @SoomSatyam I see, you've got the left join the other way. Try now – vercelli Sep 09 '16 at 15:19
  • @SoomSatyam is there a sts_id 40 & 41 in the task table? If so do you actually want it counted or not because this would count it unless another condition is set on the join to reduce the task results to sts_id 1,8,39 – Matt Sep 09 '16 at 15:28
  • @Matt It looks like he wants 40 0, 41 0 – vercelli Sep 09 '16 at 15:32
  • 1
    @vercelli that is my point if he wants 40 & 41 to be 0 but 40 & 41 actually exists in the tasks table then you would need to add AND task.sts_id in (1, 8, 39) to the on condition or 40 &/0R 41 will be > 0 – Matt Sep 09 '16 at 15:36
  • @Matt I see, he has that condition on the first query. Anyways this question is confusing and it looks the OP found the solution by himself – vercelli Sep 09 '16 at 15:38
1

You are missing the table alias in the GROUP BY clause. However, your needed result says that you need to change your join logic: the starting table should be in_list, while task should be in left outer join:

select ...
from in_list
  left outer join task
Aleksej
  • 22,443
  • 5
  • 33
  • 38
1
select 
   in_list.sts_id, 
   coalesce(count(task.sts_ID),0) mycount --changed this line
from 
   task
right outer join                          --changed this line
   in_list
  on task.sts_id = in_list.sts_id 
group by in_list.sts_id;                  -- changed this line

Reasons:

  • as in_list contains more data than task, we needed to either change the table order or make it a right join
  • Count would count all records and not return resutls you want the count from task
  • need to coalesce the results otherwise null count will return null not 0.
xQbert
  • 34,733
  • 2
  • 41
  • 62
-1

I got my answer with this query

select t2.sts_id, count(t.sts_id)
from task t, in_list t2
where t2.sts_id = t.sts_id(+)
group by t2.sts_id

Thanks,

Soom Satyam
  • 25
  • 1
  • 1
  • 7
  • 1
    please please please use the explicit join sytax implicit join especially for OUTER joins as in this case has been depricated in some rdbms such as in sql-server 2005 +. I understand oracle is different but..... http://stackoverflow.com/questions/44917/explicit-vs-implicit-sql-joins – Matt Sep 09 '16 at 15:38