CODEWITHSUNDEEP
sqloracle-database
A.Leggett
A.Leggett

Reputation: 61

Oracle SQL Aggregate Data based on Unique Column

I'm hoping what I want to achieve is possible, which is to group together a cost by the unique ID rather than repeating it for the unique IDs corresponding invoice numbers.

Current: 

Unique_ID    PO#    Inv#    Cost
1            10     1X      £500
1            10     2X      £500
1            10     3X      £500
1            10     4X      £500

Now, the cost of the PO is £500, but as I have the Invoices that make up the PO it duplicates those costs (we have a weird requirement to bring back PO Cost and not Invoice Cost). Instead I would be looking for something like:

Desired:

Unique_ID    PO#    Inv#    Cost
1            10     1X      £500
1            10     2X     
1            10     3X     
1            10     4X

Is this achievable by using a DISTINCT on the Cost column?

Upvotes: 0

Views: 29

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use row_number() :

select t.*, 
       (case when row_number() over (partition by Unique_ID, PO#, Cost order by Inv) = 1 
             then cost 
        end) as cost 
from table t;

Upvotes: 3

Related Questions