Home » SQL & PL/SQL » SQL & PL/SQL » Simplify this query
Simplify this query [message #202566] Fri, 10 November 2006 07:01 Go to next message
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 #202570 is a reply to message #202566] Fri, 10 November 2006 07:25 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Something like this?
SELECT invoice_id, SUM(amount_price) amt
FROM invoice_detail
GROUP BY invoice_id
HAVING SUM(amount_price) BETWEEN 200 AND 1000;
Re: Simplify this query [message #202584 is a reply to message #202570] Fri, 10 November 2006 07:54 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
he he.. thanks.. it's so simple and I feel so stupid for forgetting 'HAVING'!! - Dead
Thanks!!!
Re: Simplify this query [message #202603 is a reply to message #202566] Fri, 10 November 2006 09:10 Go to previous message
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

Previous Topic: build sql
Next Topic: how to find the number of days(ie the number of mondays) between 2 dates
Goto Forum:
  


Current Time: Sat Dec 10 08:50:33 CST 2016

Total time taken to generate the page: 0.25392 seconds