Home » SQL & PL/SQL » SQL & PL/SQL » Assistance in Query Construction (Oracle 10g, 11g)
Assistance in Query Construction [message #661166] Thu, 09 March 2017 13:51 Go to next message
ORA2015
Messages: 49
Registered: March 2015
Member
Hello,

I have a scenario like the below:

Date Ind. Amount
01-Jan-2016 100 $100.00
01-Jan-2016 100 $200.00
01-Jan-2016 200 $300.00

I would like to see a sample SQL query where i can see all these transactions
so that conditions will be
where the amount = $300.00 by Ind.
or where the sum of the amount = $300.00 by Ind.

Would you be able to assist?
Re: Assistance in Query Construction [message #661167 is a reply to message #661166] Thu, 09 March 2017 14:23 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: Assistance in Query Construction [message #661168 is a reply to message #661166] Thu, 09 March 2017 14:35 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals, as solution depends on it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Re: Assistance in Query Construction [message #661198 is a reply to message #661168] Fri, 10 March 2017 07:01 Go to previous messageGo to next message
ORA2015
Messages: 49
Registered: March 2015
Member
Hi i am sorry i do not have an actual table but phrased my question in terms of a possible scenario.

So basically, i was just looking for a sample sql where i can see all purchases by a customer where the amount is 300.00 for a given period or where the sum of the purchases equal 300.00
Re: Assistance in Query Construction [message #661200 is a reply to message #661198] Fri, 10 March 2017 07:06 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
Use sum in it's analytic form.
Re: Assistance in Query Construction [message #661201 is a reply to message #661200] Fri, 10 March 2017 07:48 Go to previous messageGo to next message
ORA2015
Messages: 49
Registered: March 2015
Member
[quote][/Use sum in it's analytic form.]
Hi CookieMonster,

Could you give some more clarity on your response.
I would like to see all the individual purchases still and not the sum of the purchases.
Just show the individual purchases that is equal to the 300.00 by customer or where the purchase itself is 300.00


Re: Assistance in Query Construction [message #661202 is a reply to message #661201] Fri, 10 March 2017 07:54 Go to previous messageGo to next message
BlackSwan
Messages: 26207
Registered: January 2009
Location: SoCal
Senior Member
If you don't have any table & don't know how to compose a valid SQL, how will you know that any offered statement is actually correct?

Re: Assistance in Query Construction [message #661204 is a reply to message #661198] Fri, 10 March 2017 07:55 Go to previous messageGo to next message
joy_division
Messages: 4902
Registered: February 2005
Location: East Coast USA
Senior Member
ORA2015 wrote on Fri, 10 March 2017 08:01
Hi i am sorry i do not have an actual table but phrased my question in terms of a possible scenario.
Then make a table and inserts for the three rows you have in your question.

[Updated on: Fri, 10 March 2017 07:55]

Report message to a moderator

Re: Assistance in Query Construction [message #661206 is a reply to message #661202] Fri, 10 March 2017 08:02 Go to previous messageGo to next message
ORA2015
Messages: 49
Registered: March 2015
Member
Black swan did i say it was incorrect?
Anyway thank you

Re: Assistance in Query Construction [message #661211 is a reply to message #661206] Fri, 10 March 2017 08:18 Go to previous messageGo to next message
cookiemonster
Messages: 13291
Registered: September 2008
Location: Rainy Manchester
Senior Member
sum

For each row it'll give you the sum of amount for all the rows with the same ind (use ind in the partition clause).
Then you return all rows where the result of that is >= 300.
Pesudo code
SELECT date, ind, amount
FROM (SELECT date, ind, amount, sum(amount) over (partition by ind) as sum_amt
      FROM table
     )
WHERE sum_amt >= 300

You need the nested query because you can't refer to the analytic in the where clause of the query that works it out.

Have a play around with it. If you get stuck post what you tried.

EDIT: incomplete where clause

[Updated on: Fri, 10 March 2017 08:19]

Report message to a moderator

Re: Assistance in Query Construction [message #661213 is a reply to message #661198] Fri, 10 March 2017 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 65849
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ORA2015 wrote on Fri, 10 March 2017 14:01
Hi i am sorry i do not have an actual table but phrased my question in terms of a possible scenario.

So basically, i was just looking for a sample sql where i can see all purchases by a customer where the amount is 300.00 for a given period or where the sum of the purchases equal 300.00
We don't need your actual table we just need you write CREATE TABLE and INSERT statements for your, or better a representative, example, can you do it?
Just read the sentences and links we gave you?

[Updated on: Fri, 10 March 2017 10:50]

Report message to a moderator

Re: Assistance in Query Construction [message #661215 is a reply to message #661211] Fri, 10 March 2017 10:55 Go to previous message
ORA2015
Messages: 49
Registered: March 2015
Member
Thanks cookiemonster!
I have used your example and i got through in applying it to the scenario.

Previous Topic: Cant get rid of ORA-14155 after all googling (2 threads merged by bb)
Next Topic: UTL_FILE.Put_Line carriage return.
Goto Forum:
  


Current Time: Mon Oct 22 01:06:44 CDT 2018