Solved

Group By SubQuery...

Posted on 2006-11-07
3
1,001 Views
Last Modified: 2008-01-09
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)
0
Comment
Question by:bromley_college
  • 2
3 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 17887741
what about this:
select count(person_code) emacount, x
from (
select p.person_code ,
(select byd_acc_year from brom_year_dates
where byd_fund_year = ema.FUNDING_YEAR) x
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 x
0
 

Author Comment

by:bromley_college
ID: 17887779
I am expecting something like

EMACOUNT     X
613                0607
501                0506
714                0405


However, your query just gives me:
EMACOUNT     X
613                0607


0
 

Author Comment

by:bromley_college
ID: 17887788
Nope sorry, my fault. I didn't need the line "and ema.FUNDING_YEAR = 13"

Your query works fine. Thank you :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
sql query 5 69
Best RAID for a BDD Oracle 4 62
Determine Who is Runnig my Bash Shell Script 4 64
How do I call MySQL Stored Procedure from oracle using HS link ? 5 27
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

896 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now