-1

How can i merge same Stock item value and get the sum of both value in the table

This is Query:

select Invoice_TBL.InvoiceNo
    ,Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity
    ,InvoiceItems_TBL.SellingPrice
    ,SUM(InvoiceItems_TBL.TotalAmount) as TotalAmount
from Stock_TBL
inner join InvoiceItems_TBL on Stock_TBL.StockID = InvoiceItems_TBL.StockID
inner join Invoice_TBL on InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
where (Invoice_TBL.IsTableStatus = 1)
    and (InvoiceItems_TBL.IsTableStatus = 1)
    and (Stock_TBL.IsTableStatus = 1)
    and Invoice_TBL.ClientNo = 5 --AND (Invoice_TBL.ClientNo > 0)
group by Invoice_TBL.InvoiceNo
    ,Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,InvoiceItems_TBL.SellingPrice
order by Invoice_TBL.ClientNo asc

The result is

enter image description here

But i need to display like this (Middle Table):

enter image description here

from the bottom table 402 Invoice No Merge with 335 Invoice No... i don't need to display Invoice No column and stock id column... but Same Item values should be merge and Added (Total Quantity and Total amount).. i wanted, one client in each stock item how many took (total quantity and amount should be calculated) in his/her all invoices

Please Help me... Thank you

3
  • The only things I see separating the two totals is the InvoiceNo and the SellingPrice. If you leave out those columns you will get your sum. Is that acceptable for your output?
    – SQLHound
    Commented May 20, 2015 at 21:42
  • Thanks for your answer... can you do it and show in sqlfiddle.com
    – M.I.M.F
    Commented May 20, 2015 at 22:05
  • Please help me.
    – M.I.M.F
    Commented May 21, 2015 at 4:48

1 Answer 1

0

Removal as suggested in comments above. Will this work for you?

select 
    Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
    ,SUM(InvoiceItems_TBL.Quantity) as TotalQuantity
    ,SUM(InvoiceItems_TBL.TotalAmount) as TotalAmount
from Stock_TBL
inner join InvoiceItems_TBL on Stock_TBL.StockID = InvoiceItems_TBL.StockID
inner join Invoice_TBL on InvoiceItems_TBL.InvoiceNo = Invoice_TBL.InvoiceNo
where (Invoice_TBL.IsTableStatus = 1)
    and (InvoiceItems_TBL.IsTableStatus = 1)
    and (Stock_TBL.IsTableStatus = 1)
    and Invoice_TBL.ClientNo = 5 --AND (Invoice_TBL.ClientNo > 0)
group by Invoice_TBL.ClientNo
    ,Invoice_TBL.ClientFullName
    ,InvoiceItems_TBL.StockID
    ,Stock_TBL.ItemCode
    ,Stock_TBL.ItemCategory
    ,Stock_TBL.ItemDescription
    ,Stock_TBL.Unit
    ,Stock_TBL.NoOfUnitsInACarton
order by Invoice_TBL.ClientNo asc
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.