CODEWITHSUNDEEP
sqllaraveleloquentlaravel-blade
Slygoth
Slygoth

Reputation: 333

Show grouped value for item in table

I have two table in a database Inventory and Invoices. I have a report that will show the items and the amount. What im seeing now below:

Name, Item number, Total, Amount Collected, Invoice

John Doe, 10022, $500, 1100, 100023044

John Doe, 10022, $600, 1100, 100023044

John can have 2 items on the same invoice. What I'm trying to do is show how much they collected in one column and not the two of them.

What i want to see is 

John Doe, 10022, $500, 1100, 100023044

John Doe, 10022, $600, **blank**, 100023044

I want to see the amount collected group on the report for all the items with multiple items attached to the same invoice

Upvotes: 1

Views: 40

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

One method uses row_number():

select Name, Item, Total,
       (case when seqnum = 1 then Amount end) as Amount,
       Invoice
from  (select t.*,
              row_number() over (partition by invoice order by amount) as seqnum
       from t
      ) t
order by invoice, seqnum

Upvotes: 1

Related Questions