bromley_college
asked on
Group By SubQuery...
Hi experts,
I would like to group by the subquery but when I try I get the error "ORA-22818: subquery expressions not allowed here."
What am I doing wrong or what is the alternative? Thanks.
My query:
select distinct
count(p.person_code) as emacount,
(select byd_acc_year from brom_year_dates
where byd_fund_year = ema.FUNDING_YEAR)
from people p, ema
where p.EMA_ID is not null
and ema.PERSON_CODE = p.PERSON_CODE
and ema.FUNDING_YEAR = 13
group by (select byd_acc_year from brom_year_dates
where byd_fund_year = ema.FUNDING_YEAR)
I would like to group by the subquery but when I try I get the error "ORA-22818: subquery expressions not allowed here."
What am I doing wrong or what is the alternative? Thanks.
My query:
select distinct
count(p.person_code) as emacount,
(select byd_acc_year from brom_year_dates
where byd_fund_year = ema.FUNDING_YEAR)
from people p, ema
where p.EMA_ID is not null
and ema.PERSON_CODE = p.PERSON_CODE
and ema.FUNDING_YEAR = 13
group by (select byd_acc_year from brom_year_dates
where byd_fund_year = ema.FUNDING_YEAR)
ASKER CERTIFIED SOLUTION
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nope sorry, my fault. I didn't need the line "and ema.FUNDING_YEAR = 13"
Your query works fine. Thank you :)
Your query works fine. Thank you :)
ASKER
EMACOUNT X
613 0607
501 0506
714 0405
However, your query just gives me:
EMACOUNT X
613 0607