Need to find overall totals grouped by item name

I have a table in which StoreNumbers are set as column names. Those columns contain the items shipped. Now, I need to write a query to fetch the overall totals store wise. I wrote a query but it does not group the items. It shows all the values individually. Now, I need to show only one row for a store number by calculating the overall total. Please help me how I can do that. My Query is:

(select DMM_Name, Store_Number, Location,Distbn_Date, Sum(CAST([302] as Numeric)) TQty, PJ_Item_M.SellingPrice,
(Sum(CAST([302] as Numeric))* PJ_Item_M.SellingPrice) as total
from PJ_Location_M, PJ_Matrix_RowDstbn_T, PJ_Item_M where
PJ_Item_M.ItemNumber = PJ_Matrix_RowDstbn_T.ItemNumber and
PJ_Item_M.Variation = PJ_Matrix_RowDstbn_T.Variation and
Distbn_Date >= '2007-06-06' and Distbn_Date <= '2007-06-16' and PJ_Location_M.Store_Number='302'
group by Distbn_Date, PJ_Item_M.SellingPrice,DMM_Name, Store_Number, Location)
Union (select DMM_Name, PJ_Location_M.Store_Number, Location,
SplOrd_Date, Sum(Qty) TQty,
Price,(Sum(Qty) * Price )as total
from PJ_Location_M,PJ_Special_Order_T where SplOrd_Date >= '2007-06-06' and
SplOrd_Date <= '2007-06-16' and PJ_Special_Order_T.Store_Number = '302' and
PJ_Special_Order_T.Store_Number=PJ_Location_M.Store_Number
group by SplOrd_Date, Price,DMM_Name, PJ_Location_M.Store_Number, Location)

302 is the store number, I need to make the overall total as total of

. (Sum(CAST([302] as Numeric))* PJ_Item_M.SellingPrice) as total
Please modify my query so that I can get overall totals store number wise.

It would be much easier if that was a child-table instead - and you could add a new store without having to modify your database.

Also a bit bothered that you have having to CAST it - what is the datatype of the [302] column?

Also using NUMERIC with no Precision or Scale uses the defaults. Personally I'd prefer to see those explicitly stated, although I doubt you have Qty big enough to need to increase the Precision default ...

I don't know if it is the answer to your question, but you could just "wrap" your query with

SELECT SUM(X.total) AS [GrandTotal]
FROM
(
    ... your query here ...
) AS X

If your problem is "reusing" the SUM from your sub-query MULTIPLE times in the outer SELECT then I normally do that like this:

SELECT Col1, Col2, [302_Total], [302_Total] * Price AS Total
FROM MyMainTable AS M
CROSS APPLY
(
    SELECT SUM(SomeCol) AS  [302_Total]
    FROM MyChildTable AS C
    WHERE C.SomeID = M.SomeID
) AS X

or you could use a CTE

If you want both the detail from your query AND a total (e.g. at the bottom of the report) one way is to output the results from your query to a temporary table, so:

SELECT Col1, Col2, ...
INTO #MyTempTable
FROM ...

and then output like this:

SELECT Col1, Col2, ..., total
FROM #MyTempTable
UNION ALL
SELECT 'Total', NULL, ..., SUM(total)
FROM #MyTempTable

you could probably also do that with a ROLLUP