Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Group by with subqueries

739464Feb 24 2011 — edited Feb 24 2011
I have a query that will use an aggregate function (count), as well as multiple subqueries to return the necessary data. I'm having trouble figuring out how to set up the group by clause for this type of situation. Here's my query:
select trade_show.sales_source_id,
       trade_show.name, 
       trade_show.start_dt, 
       trade_show.end_dt, 
       count(a.user_id) as leads, 
       (select count(*) from download_history where download_history.user_id = a.user_id and download_history.patch_id is null and trunc(download_history.download_dt) >= trunc(trade_show.start_dt) and trunc(download_history.download_dt) <= trunc(trade_show.start_dt) + 14) as dlds_2wk
    from user_sales_source a, 
         trade_show
  where trade_show.sales_source_id = a.sales_source_id  
  and trade_show.deleted_ind = 0
  and trunc(trade_show.start_dt) >= add_months(sysdate, -6)
  and trunc(trade_show.start_dt) <= add_months(sysdate, 3)
  and a.deleted_ind = 0
  and a.sales_source_dt = (select min(user_sales_source.sales_source_dt) 
                              from user_sales_source
                            where user_sales_source.user_id = a.user_id
                            and user_sales_source.deleted_ind = 0)
group by trade_show.sales_source_id,
         trade_show.name, 
         trade_show.start_dt, 
         trade_show.end_dt;
Basically what I'm looking for is the number of leads and downloads for each trade show. If I remove the dlds2wk subquery, everything works fine. Adding in the subquery results in a ORA-00979: not a GROUP BY expression error, and since I can't add a subquery to the group by clause, I'm stuck.

Any help would be appreciated, thanks.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 24 2011
Added on Feb 24 2011
6 comments
9,165 views