| Simplify this query [message #202566] |
Fri, 10 November 2006 07:01  |
a_developer
Messages: 194
Registered: January 2006
|
Senior Member |
|
|
I have a query to get the sums that are within a range of amounts. Here's how to create/populate the sample table:
create table invoice_detail
(invoice_id number,
amount_price number);
insert into invoice_detail values (1, 100);
insert into invoice_detail values (1, 200);
insert into invoice_detail values (1, 300);
insert into invoice_detail values (2, 20);
insert into invoice_detail values (2, 10);
insert into invoice_detail values (2, 150);
insert into invoice_detail values (2, 50);
insert into invoice_detail values (3, 90);
insert into invoice_detail values (3, 100);
insert into invoice_detail values (4, 600);
insert into invoice_detail values (4, 700);
insert into invoice_detail values (5, 100);
insert into invoice_detail values (5, 100);
insert into invoice_detail values (5, 100);
insert into invoice_detail values (5, 100);
My query is
select a.invoice_id, a.amt
from (select sum(amount_price) amt,
invoice_id
from invoice_detail
group by invoice_id) a
where a.amt between 200 and 1000
and correctly returns:
INVOICE_ID AMT
---------- ---
1 600
2 230
5 400
My question is: Is there a simpler way to write the query without using a subquery?
Thanks in advance.
[Updated on: Fri, 10 November 2006 07:03] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Simplify this query [message #202603 is a reply to message #202566] |
Fri, 10 November 2006 09:10  |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
Thanks for providing Create and Insert statments.
You need the HAVING clause - it works like a WHERE clause, but for aggregate functions.
select invoice_id
,sum(amount_price) amt
from invoice_detail
having sum(amount_price) between 200 and 1000
group by invoice_id;
[Guessed someone would beat me to it - Actually had to do the job I get paid for in the middle of posting that one.]
[Updated on: Fri, 10 November 2006 09:11] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
