Reference parent query column in subquery (Oracle)

You can do it in a sub-query but not in a join. In your case I don't see any need to. You can put it in the join condition.

select i.itemno, i.group  from item i  left outer join ( select group, itemno                      from attribute b                     group by group itemno ) a    on a.group = i.group    and i.itemno = a.itemno where i.itemno = 12345

The optimizer is built to deal with this sort of situation so utilise it!

I've changed the count(1) to a group by as you need to group by all columns that aren't aggregated.

I'm assuming that your actual query is more complicated than this as with the columns you're selecting this is probably equivilent to

select itemno, group  from item where itemno = 12345

You could also write your sub-query with an analytic function instead. Something like count(*) over ( partition by group).

As an aside using a keyword as a column name, in this case group is A Bad Idea ^TM. It can cause a lot of confusion. As you can see from the code above you have a lot of groups in there.

So, based on your SQL-Fiddle, which I've added to the question I think you're looking for something like the following, which doesn't look much better. I suspect, given time, I could make it simpler. On another side note explicitly lower casing queries is never worth the hassle it causes. I've followed your naming convention though.

with sub_query as ( select count(*) - count(icav.itemno) as error      , count(icav.itemno) as ok      , min(itemno) over () as itemno      , tariffgroup      , tariffno   from itemattribute ia    left outer join icav      on ia.attributeid = icav.attributeid   group by icav.itemno         , tariffgroup         , tariffno  )     select ic.itemno, ic.country, ic.imgroup, ic.imtariff         , sum(im.error) as "imerror", sum(im.ok) as "imok"         , ic.exgroup, ic.extariff         , sum(ex.error) as "exerror", sum(ex.ok) as "exok"      from itemcountry ic      left outer join sub_query im         on ic.imgroup = im.tariffgroup       and ic.imtariff = im.tariffno       and ic.itemno = im.itemno      left outer join sub_query ex         on ic.exgroup = ex.tariffgroup       and ic.extariff = ex.tariffno       and ic.itemno = ex.itemno     where ic.itemno = 12345     group by ic.itemno, ic.country            , ic.imgroup, ic.imtariff            , ic.exgroup, ic.extariff           ;

You can put WHERE attribute.itemno=item.itemno inside the subquery. You are going to filter the data anyway, filtering the data inside the subquery is usually faster too.