| Aggregate and Subquery in Select Clause [message #372826] |
Mon, 12 March 2001 09:45  |
JD
Messages: 7
Registered: March 2001
|
Junior Member |
|
|
I have a query where I am summing revenue over a period of time (year-to-date). In the select clause of that query I have a subquery that does exactly the same thing except of a different date range. This query works in informix, but I get a "Not a Group By Expression" from oracle. I've had several SQL guru's to look at it and it's there opinion that it should work.
Can you have a subquery in the same select statement with an aggregate function in oracle?
I can send the sql if necessary.
Thanks!!
JD
|
|
|
|
|
|
| Re: Aggregate and Subquery in Select Clause [message #372829 is a reply to message #372828] |
Mon, 12 March 2001 11:24  |
JD
Messages: 7
Registered: March 2001
|
Junior Member |
|
|
Here is the query.
SELECT ps_mkt_cust_data.dept_id, ps_mkt_cust_master.corp_cust_id,
ps_mkt_corp_cust.corp_cust_name, (Sum(ps_mkt_cust_data.revenue)) As YTDRevenue,
(
SELECT Sum(a.Revenue)
FROM ps_mkt_cust_data a, ps_mkt_cust_master b
WHERE a.cust_id = b.cust_id AND
a.dept_id = 'VA002' AND
b.corp_cust_id = ps_mkt_cust_master.corp_cust_id AND
a.revenue_dt BETWEEN '01-JUL-00' AND '31-JUL-00'
) As CurrentRevenue
FROM ps_mkt_cust_data, ps_mkt_cust_master, ps_mkt_corp_cust
WHERE ps_mkt_cust_data.cust_id = ps_mkt_cust_master.cust_id AND
ps_mkt_cust_master.corp_cust_id = ps_mkt_corp_cust.corp_cust_id AND
ps_mkt_cust_data.dept_id = 'VA002' AND
ps_mkt_cust_data.revenue_dt BETWEEN '01-JAN-00' AND '31-JUL-00'
GROUP BY ps_mkt_cust_data.dept_id, ps_mkt_corp_cust.corp_cust_name,
ps_mkt_cust_master.corp_cust_id
HAVING Sum(Revenue)<>0
ORDER BY ps_mkt_cust_data.dept_id, YTDRevenue DESC
Thanks for you help!!
JD
|
|
|
|
| Re: Aggregate and Subquery in Select Clause [message #372830 is a reply to message #372829] |
Mon, 12 March 2001 11:58 |
me
Messages: 66
Registered: August 2000
|
Member |
|
|
use an inline query as below:
SELECT ps_mkt_cust_data.dept_id, ps_mkt_cust_master.corp_cust_id,
ps_mkt_corp_cust.corp_cust_name, (Sum(ps_mkt_cust_data.revenue)) As YTDRevenue,
cr.CurrentRevenue
FROM ps_mkt_cust_data, ps_mkt_cust_master, ps_mkt_corp_cust,
(SELECT Sum(a.Revenue) as CurrentRevenue, a.cust_id
FROM ps_mkt_cust_data a, ps_mkt_cust_master b
WHERE a.cust_id = b.cust_id AND
a.dept_id = 'VA002' AND
b.corp_cust_id = ps_mkt_cust_master.corp_cust_id AND
a.revenue_dt BETWEEN '01-JUL-00' AND '31-JUL-00'
) CR
WHERE ps_mkt_cust_data.cust_id = ps_mkt_cust_master.cust_id AND
ps_mkt_cust_master.corp_cust_id = ps_mkt_corp_cust.corp_cust_id AND
cr.cust_id = ps_mkt_cust_data.cust_id AND
ps_mkt_cust_data.dept_id = 'VA002' AND
ps_mkt_cust_data.revenue_dt BETWEEN '01-JAN-00' AND '31-JUL-00'
GROUP BY ps_mkt_cust_data.dept_id, ps_mkt_corp_cust.corp_cust_name,
ps_mkt_cust_master.corp_cust_id,
cr.CurrentRevenue
HAVING Sum(Revenue)<>0
ORDER BY ps_mkt_cust_data.dept_id, YTDRevenue DESC
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
