Rows in which the final quantity is equal to the initial_quantity-1
731844Nov 3 2009 — edited Nov 4 2009Hi,
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