Add Grand Total of all records as a Column SQL
I have a Column that calculates Cost for each Record. I need to add a Column that has the Grand Total for all records to be used in a calculation.
SELECT CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' ) THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' ) THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' ) THEN ( 5 )
ELSE ( 6 )
END AS SeasonNumber ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd, DATEDIFF(dd, 0, trx_time), 0) <= DATEADD(yy,
-1, '06/30/2017') )
), 0) * ( SC.std_cost ) AS PrvCost ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd, DATEDIFF(dd, 0, trx_time), 0) <= DATEADD(yy,
-1, '06/30/2017') )
), 0) AS IvtyQtyPrvYr ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost
FROM zzxseasr AS Sea
INNER JOIN zzxscolr AS SC ON Sea.season = SC.season
GROUP BY CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' ) THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' ) THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' ) THEN ( 5 )
ELSE ( 6 )
END ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost
SELECT CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' ) THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' ) THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' ) THEN ( 5 )
ELSE ( 6 )
END AS SeasonNumber ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd, DATEDIFF(dd, 0, trx_time), 0) <= DATEADD(yy,
-1, '06/30/2017') )
), 0) * ( SC.std_cost ) AS PrvCost ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd, DATEDIFF(dd, 0, trx_time), 0) <= DATEADD(yy,
-1, '06/30/2017') )
), 0) AS IvtyQtyPrvYr ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost
FROM zzxseasr AS Sea
INNER JOIN zzxscolr AS SC ON Sea.season = SC.season
GROUP BY CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' ) THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' ) THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' ) THEN ( 5 )
ELSE ( 6 )
END ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost
ASKER
Your answer appears to be spot on. Sorry in my delay. I attempted to apply your logic to my entire query however I failed to account for the fact that not only do I need PrYrCost but CurYrCost as well. However I think I'm just making matters worse. Here is what I'm attempting to do.
WITH cte
AS ( SELECT CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' )
THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' )
THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' )
THEN ( 5 )
ELSE ( 6 )
END AS SeasonNumber ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0, trx_time),
0) <= DATEADD(yy, -1,
'06/30/2017') )
), 0) AS IvtyQtyPrvYr ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0, trx_time),
0) <= DATEADD(yy, -1,
'06/30/2017') )
), 0) * ( SC.std_cost ) AS PrvYrCost ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS CurIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0, trx_time),
0) <= '06/30/2017' )
), 0) AS IvtyQtyCurYr ,
ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS CurIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0, trx_time),
0) <= '06/30/2017' )
), 0) * ( SC.std_cost ) AS CurYrCost ,
OTS.Open_Orders + OTS.Picked AS OpenQty ,
OTS.OTSOpen AS OTSQty ,
OTS.OTSOpen * SC.std_cost AS Ext_OTS_Cost ,
( OTS.Open_Orders + OTS.Picked ) * SC.std_cost AS Ext_Open_Cost ,
ISNULL(dbo.KLLfn_Get_Total_Open_Amount_For_Style(SC.division,
SC.style,
SC.color_code,
SC.lbl_code,
SC.dimension), 0) AS OpenDollars ,
CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' )
THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' )
THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' )
THEN ( 5 )
ELSE ( 6 )
END AS Expr1
FROM zzxseasr AS Sea
INNER JOIN zzxscolr AS SC ON Sea.season = SC.season
AND ( Sea.division IN (
@Divisions ) )
LEFT OUTER JOIN ( SELECT division ,
style ,
color_code ,
lbl_code ,
DIMENSION ,
SUM(Open_Orders) AS Open_Orders ,
SUM(Picked) AS Picked ,
SUM(OTSWIP) AS OTSOpen
FROM [KLL Cut and Sold OTS Sum w/ DMPK]
WHERE ( IvtyLocation IN (
@Locations ) )
GROUP BY division ,
style ,
color_code ,
lbl_code ,
DIMENSION
) AS OTS ON SC.division = OTS.division
AND SC.style = OTS.style
AND SC.color_code = OTS.color_code
AND SC.lbl_code = OTS.lbl_code
AND SC.dimension = OTS.DIMENSION
GROUP BY CASE WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SPRING' )
THEN ( 1 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 6) = 'SUMMER' )
THEN ( 2 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 20'
OR SUBSTRING(Sea.seas_name, 1, 8) = 'FALL I 2'
) THEN ( 3 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'FALL 2 '
OR SUBSTRING(Sea.seas_name, 1, 7) = 'FALL II'
) THEN ( 4 )
WHEN ( SUBSTRING(Sea.seas_name, 1, 7) = 'HOLIDAY' )
THEN ( 5 )
ELSE ( 6 )
END ,
Sea.season ,
Sea.seas_name ,
SC.division ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension ,
SC.std_cost ,
OTS.Open_Orders + OTS.Picked ,
OTS.OTSOpen
HAVING ( SC.division IN ( @Divisions ) )
AND ( Sea.season IN ( @Seasons ) )
AND ( ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS PrvIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0,
trx_time), 0) <= DATEADD(yy,
-1, '06/30/2017') )
), 0) <> 0 )
OR ( SC.division IN ( @Divisions ) )
AND ( Sea.season IN ( @Seasons ) )
AND ( ISNULL(( SELECT SUM(total_qty) AS qty
FROM zzxinvtr AS CurIV
WHERE ( division = SC.division )
AND ( style = SC.style )
AND ( color_code = SC.color_code )
AND ( lbl_code = SC.lbl_code )
AND ( dimension = SC.dimension )
AND ( DATEADD(dd,
DATEDIFF(dd, 0,
trx_time), 0) <= '06/30/2017' )
), 0) <> 0 )
ORDER BY SeasonNumber ,
Sea.season ,
SC.style ,
SC.color_code ,
SC.lbl_code ,
SC.dimension
)
SELECT cte.* ,
b.total
FROM cte
CROSS APPLY ( SELECT SUM(PrvYrCost) total
FROM cte
) bASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This worked perfectly. Thank You. Of course the parameters have changed however you provided me with what I asked for twice now. I guess what they want to see is a percentage. PrvYrCost / PrvYrCost_Total * 100 AS Percent of PrvYear AND CurYrCost / CurYrCost_Total * 100 AS Percent of CurYear
ASKER
This worked perfectly. Thank You. Of course the parameters have changed however you provided me with what I asked for twice now. I guess what they want to see is a percentage. PrvYrCost / PrvYrCost_Total * 100 AS Percent of PrvYear AND CurYrCost / CurYrCost_Total * 100 AS Percent of CurYear
(if the field to calculate the grand total is PrvCost)
Open in new window