Search code examples
sqlsumsybasesqlanywhere

Summing Data on Two Rows with a Similar Identifier

I'm attempting to sum a column 'Quantity' in our database whenever an Item ID is either itself or if its itself with a zero in front (whether the ID is 2447 or 02447 as an example in this case):

I started with the following to get the sums of the IDs:

SELECT
"TJ_TransactionJournalDetail"."TLI_ScanCode" As LineItemID,
"TJ_StockInventory"."INV_ScanCode" As InventoryID,
"TJ_TransactionJournalDetail"."TLI_ReceiptAlias" As ReceiptAlias,
"TJ_StockInventory"."INV_Name" As ItemName,
"TJ_TransactionJournalDetail"."TLI_LIT_FK" As LineDiscount,
SUM("TJ_TransactionJournalDetail"."TLI_Quantity") AS Quantity

FROM "TJ_StockInventory" LEFT OUTER JOIN "TJ_TransactionJournalDetail" ON "TJ_StockInventory"."INV_PK" = "TJ_TransactionJournalDetail"."INV_PK"
WHERE ecrs.TJ_TransactionJournalDetail.TLI_StartTime > '2020-01-17 00:00:00.000'

AND ecrs.TJ_TransactionJournalDetail.TLI_EndTime < '2020-01-19 23:59:59.999'
AND INV_DPT_FK = 49
AND "TJ_TransactionJournalDetail"."TLI_LIT_FK" = 1
GROUP BY LineItemID,InventoryID,ReceiptAlias,ItemName,LineDiscount
ORDER BY InventoryID;

These are the results the quantity of which I'm attempting to combine:

LineItemID,InventoryID,ReceiptAlias,ItemName,LineDiscount,Quantity
    '2447','02447         ','DELI-BEAR CLAW EA','Bear Claw',1,1.0000
    '02447','02447         ','DELI-BEAR CLAW EA','Bear Claw',1,30.0000

What I'm looking for:

'2447','02447         ','DELI-BEAR CLAW EA','Bear Claw',1,31.0000

-or-

'02447','02447         ','DELI-BEAR CLAW EA','Bear Claw',1,31.0000

as long as the quantity is 31.

I basically want to combine the quantity of two rows if the LineItemID is the same as "0" concatenated with the LineItemID on another line. Or another way of possibly accomplishing it would be to combine all items with the same InventoryID, but that is in the StockInventory table, not the TransactionJournal table which has the quantity that I'm summing.

And have tried a number of solutions, first I tried a CASE statement but couldn't figure out how to apply it across rows:

....
SUM (
    CASE WHEN ( "TJ_StockInventory"."INV_ScanCode" = STRING('0',"TJ_TransactionJournalDetail"."TLI_ScanCode",'         ') )
        THEN "TJ_TransactionJournalDetail"."TLI_Quantity"
        ELSE 0 END
    ) AS Quantity
FROM "TJ_StockInventory" LEFT OUTER JOIN     
....

I also tried partitioning by ItemID to combine the quantity when the InventoryIDs were the same:

....
SUM("TJ_TransactionJournalDetail"."TLI_Quantity") over (partition by "TJ_StockInventory"."INV_ScanCode") AS Quantity
....

But neither of these solutions worked. I chose to "else 0" the case statement just to narrow it down to that one item, but in all cases it kept the lines separate and did not combine the quantities.

I've looked at a number of tutorials but none seem to deal with this specific case, and I haven't found anything that was hinting at a solution for this. That being said, I have difficulty wrapping my mind around database programming at times and am open to the idea that I may be approaching this in the incorrect way.

A couple of pseudocode examples of what I'm looking for

if LineItemID of this row = CONCAT('0', LineItemID) of another row
then sum the quantities of those rows

-or-

if InventoryID of this row and InventoryID of another row are equal
then sum them even if the LineItemIDs are different

Any help, pointers or directions to examples or docs online that could help with this would be great!

Thank you!

Solution

  • You seem to want to remove "LineItemID" from the aggregation:

    SELECT MAX(tjd."TLI_ScanCode") As LineItemID,
       si."INV_ScanCode" As InventoryID,
       tjd."TLI_ReceiptAlias" As ReceiptAlias,
       si."INV_Name" As ItemName,
       tjd."TLI_LIT_FK" As LineDiscount,
       SUM(tjd."TLI_Quantity") AS Quantity
FROM "TJ_StockInventory" si LEFT OUTER JOIN 
     "TJ_TransactionJournalDetail" tjd
     ON si."INV_PK" = tjd."INV_PK"
WHERE tjd.TLI_StartTime > '2020-01-17' AND
      tjd.TLI_EndTime < '2020-01-20' AND
      INV_DPT_FK = 49 AND 
      tjd."TLI_LIT_FK" = 1
GROUP BY InventoryID, ReceiptAlias, ItemName, LineDiscount
ORDER BY InventoryID;