Adding Calculated Columns to a SQL Group By query

Matthew Moran

I'm trying to learn how to run a couple calculated fields on columns calculated in a group by query.

I'm running a SQL query via (ODBC) in Microsoft Access.

SELECT 
    MasterItemID, ItemID,
    SUM(SWITCH(JournalEX = 18, StockQtyRec, JournalEX <> 18, 0)) AS orderedByZG,
    SUM(SWITCH(JournalEX = 19, StockQty, JournalEX <> 19, 0)) AS orderedFromZG,
    ABS(SUM(SWITCH(JournalEX = 9 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 9 AND IncludeInInvLedger <> 1, 0))) AS returnedItems,
    SUM(SWITCH(JournalEX = 14 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 14 AND IncludeInInvLedger <> 1, 0)) AS inventoryAdjustments
FROM 
    qryHdrtoLineItem
GROUP BY 
    MasterItemID, ItemID;

I need to include two additional columns, each a calculation of the grouped by fields but which are not aggregated totals and do not run in the group by query.

quantityOnHand is orderedByZG - orderedFromZG - returnedItems + inventoryAdjustments

returnPercentage is returnedItems / orderedFromZG * 100

I have it working by saving the above query and running a query referencing the column Names and then adding the calculated columns.

Can this be done via a CTE? Is there a better way to accomplish this?

Note: In Access odbc the switch function is the same as Case.

JNevill

With a subquery:

SELECT 
   MasterItemId,
   ItemID,
   orderedByZG - orderedFromZG - returnedItems + inventoryAdjustments as QtyOnHand,
   returnedItems / orderedFromZG * 100 as returnPercentage
FROM 
   (

    SELECT 
        MasterItemID, ItemID,
        SUM(SWITCH(JournalEX = 18, StockQtyRec, JournalEX <> 18, 0)) AS orderedByZG,
        SUM(SWITCH(JournalEX = 19, StockQty, JournalEX <> 19, 0)) AS orderedFromZG,
        ABS(SUM(SWITCH(JournalEX = 9 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 9 AND IncludeInInvLedger <> 1, 0))) AS returnedItems,
        SUM(SWITCH(JournalEX = 14 AND IncludeInInvLedger = 1, StockQty, JournalEX <> 14 AND IncludeInInvLedger <> 1, 0)) AS inventoryAdjustments
    FROM 
        qryHdrtoLineItem
    GROUP BY 
        MasterItemID, ItemID

) sub

Collected from the Internet

Please contact [email protected] to delete if infringement.