Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Rows in which the final quantity is equal to the initial_quantity-1

731844Nov 3 2009 — edited Nov 4 2009
Hi,

I have a table with the following columns: account_id, country_code, initial quantity, final quantity, unit.
For example:
account_id country_code initital quantity final quantity unit
555 FR 1 5 1
555 FR 6 8 1
555 FR 11 12 1

I want the rows in which the final quantity is equal to the initial_quantity-1.

For this I have the following query
select a.country_code,
a.account_id,
a.initial_quantity,
a.final_quantity,
a.unit,
from tableX a
inner join tableX b
on a.final_quantity+1=b.initial_quantity and a.unit=b.unit
where
and a.country_code=b.country_code
and a.account_id=b.account_id

Running the query, the result is
555 FR 1 5 1

But I want it to also display row:
555 FR 6 8 1

Because I eventually want to merge this in a single row
555 FR 1 8 1

Any ideas?

Thanks in advance.

Kind regards
This post has been answered by Solomon Yakobson on Nov 3 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 2 2009
Added on Nov 3 2009
10 comments
1,027 views