SQL Sum Column before inserting into #temp

Question

In my SPROC a table named #temp1 contains the following columns:

#temp1 (StoreId, StoreDesc, ReservedQty, AvgPrice, QtyOnHand)

My question is based on the following query

INSERT INTO #temp2 (StoreId, StoreDesc, CommittedQty)
(SELECT  StoreId,  StoreDesc, 
 CASE WHEN ReservedQty > QtyOnHand THEN
  sum(QtyOnHand * AvgPrice) 
 ELSE
  sum(ReservedQty * AvgPrice) 
 END AS CommittedQty
 FROM #temp1 
 GROUP BY StoreId, StoreDesc, QtyOnHand, ReservedQty)

A sample result set looks like this:

StoreId     StoreDesc CommittedQty 
C4ED0D8B-22CF-40FE-8EF9-7FD764310C94    FramersBranch 0
C4ED0D8B-22CF-40FE-8EF9-7FD764310C94    FarmersBranch 88978
C4ED0D8B-22CF-40FE-8EF9-7FD764310C94    FarmersBranch 0
C4ED0D8B-22CF-40FE-8EF9-7FD764310C94    FarmersBranch 3152
6369D3A6-83BC-4BB0-9A25-86838CD2B7BA    Woodlands 5582
6369D3A6-83BC-4BB0-9A25-86838CD2B7BA    Woodlands 389

Unfortunatly since I have to GROUP BY the QtyOnHand & ReservedQty columns in my CASE statement I get multiple rows for each StoreId.

I would like to know if there is a simple way for me to sum the results (again) based on the CommittedQty so that I may get the following result set I desire:

StoreId v    StoreDesc CommittedQty 
C4ED0D8B-22CF-40FE-8EF9-7FD764310C94    FramersBranch 92130
6369D3A6-83BC-4BB0-9A25-86838CD2B7BA    Woodlands 5971

I realize I could use another temp table but wondered if there was an easier way to accomplish this inside the SELECT statement

Answer 1

SELECT  StoreId,  StoreDesc, 
        SUM(
        CASE
        WHEN ReservedQty > QtyOnHand THEN
                QtyOnHand * AvgPrice 
        ELSE
                ReservedQty * AvgPrice
        END
        ) AS CommittedQty
FROM    #temp1 
GROUP BY
        StoreId, StoreDesc

Answer 2

I wouldn't use a temp table. My guess is that you're already using at least one too many :)

You need to put the SUM around your whole CASE statement:

SUM(AvgPrice *
     CASE
          WHEN ReservedQty > QtyOnHand THEN QtyOnHand
          ELSE ReservedQty
     END)

Answer 3

First things first: If you can avoid using #temp and ##temp tables, you should. They are evil nasty little things that cause global warming and flash flooding and are responsible for all sorts of green house emissions. (:

Jokes apart, unless it is absolutely necessary, get rid of the temp tables and replace it with a sub query if you have to.

You can simply wrap another select around this statement

INSERT INTO #temp2 (StoreId, StoreDesc, CommittedQty)
SELECT StoreId, StoreDesc, Sum (CommittedQty)
FROM 
(SELECT         StoreId,  StoreDesc, 
        CASE WHEN ReservedQty > QtyOnHand THEN
                sum(QtyOnHand * AvgPrice) 
        ELSE
                sum(ReservedQty * AvgPrice) 
        END AS CommittedQty
        FROM #temp1 
        GROUP BY StoreId, StoreDesc, QtyOnHand, ReservedQty)
)
GROUP BY StoreId, StoreDesc