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.