Assistance in Query Construction [message #661166] |
Thu, 09 March 2017 13:51 |
|
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 #661211 is a reply to message #661206] |
Fri, 10 March 2017 08:18 |
cookiemonster
Messages: 13917 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 |
|
Michel Cadot
Messages: 68625 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
ORA2015 wrote on Fri, 10 March 2017 14:01Hi 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
|
|
|
|