Frank Bluemke
Reputation: 33
Sum of 2 fields shows different value if i add a group by field
This is in Sql Server 2019
I have a large query composed of 1 base table containing products (variantID is primary key) and 8 subqueries which summarize sales and return data for different time periods to the VariantID level (again, variantID becomes primary key). All of the subqueries are joined to the product table via a left outer join.
I am getting strange results when doing a sum on 2 fields: CASE WHEN SUM(V_LW_SKU_SLS.UnitsSold)-sum(V_LW_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.UnitsSold)-sum(V_LW_RETURNS.quantity) END AS LW_NET_DEMAND_QTY
If i individually sum units sold and quantity returned as separate columns those values are correct. The above statement is correct if there are no group by section (and no other fields in the select statement), however if i add group by fields the value becomes completely wrong.
Since the values as individual columns are correct even when the group by statement is there, I did not go down the rabbit hole of reviewing all the sub queries.
What could be causing different results in the above expression just be adding group by? Below is the full (and very large) query:
SELECT [Group ID]
SUM(dbo.D_PRODUCTS.OH_QTY) AS OnHandQty, CASE WHEN SUM(V_LW_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.UnitsSold) END AS LW_SALE_QTY,
CASE WHEN SUM(V_LW_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.NetAmt) END AS LW_SALE_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.UnitsSold) END AS LWLY_SALE_QTY,
CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetAmt) END AS LWLY_SALE_AMT, CASE WHEN SUM(V_MTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.UnitsSold) END AS MTD_SALE_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetAmt) END AS MTD_SALE_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.UnitsSold) END AS MTDLY_SALE_QTY,
CASE WHEN SUM(V_MTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetAmt) END AS MTDLY_SALE_AMT, CASE WHEN SUM(V_QTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.UnitsSold) END AS QTD_SALE_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetAmt) END AS QTD_SALE_AMT, CASE WHEN SUM(V_QTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.UnitsSold) END AS QTDLY_SALE_QTY,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetAmt) END AS QTDLY_SALE_AMT, CASE WHEN SUM(V_YTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.UnitsSold) END AS YTD_SALE_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetAmt) END AS YTD_SALE_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.UnitsSold) END AS YTDLY_SALE_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetAmt) END AS YTDLY_SALE_AMT, CASE WHEN SUM(V_LW_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.ShipQty - v_lw_returns.quantity)
END AS LW_NET_SHIP_QTY, CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.ShipQty - v_lwly_returns.quantity) END AS LWLY_NET_SHIP_QTY, CASE WHEN SUM(V_MTD_SKU_SLS.ShipQty) IS NULL
THEN 0 ELSE SUM(V_MTD_SKU_SLS.ShipQty - v_mtd_returns.quantity) END AS MTD_NET_SHIP_QTY, CASE WHEN SUM(V_MTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.ShipQty - v_mtdly_returns.quantity) END AS MTDLY_NET_SHIP_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.ShipQty - v_qtd_returns.quantity) END AS QTD_NET_SHIP_QTY, CASE WHEN SUM(V_QTDLY_SKU_SLS.ShipQty) IS NULL
THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.ShipQty - v_qtdly_returns.quantity) END AS QTDLY_NET_SHIP_QTY, CASE WHEN SUM(V_YTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.ShipQty - v_ytd_returns.quantity) END AS YTD_NET_SHIP_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.ShipQty - v_ytdly_returns.quantity) END AS YTDLY_NET_SHIP_QTY, CASE WHEN SUM(V_LW_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_LW_SKU_SLS.NetShip$ - v_lw_returns.subtotal) END AS LW_NET_SHIP_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetShip$ - v_lwly_returns.subtotal) END AS LWLY_NET_SHIP_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetShip$ - v_mtd_returns.subtotal) END AS MTD_NET_SHIP_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetShip$ - v_mtdly_returns.subtotal) END AS MTDLY_NET_SHIP_AMT, CASE WHEN SUM(V_QTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetShip$ - v_qtd_returns.subtotal) END AS QTD_NET_SHIP_AMT,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetShip$ - v_qtdly_returns.subtotal) END AS QTDLY_NET_SHIP_AMT, CASE WHEN SUM(V_YTD_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetShip$ - v_ytd_returns.subtotal) END AS YTD_NET_SHIP_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetShip$ - v_ytdly_returns.subtotal) END AS YTDLY_NET_SHIP_AMT,
CASE WHEN SUM(V_LW_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.ShipQty) END AS LW_SHIP_QTY, CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.ShipQty) END AS LWLY_SHIP_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.ShipQty) END AS MTD_SHIP_QTY, CASE WHEN SUM(V_MTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.ShipQty) END AS MTDLY_SHIP_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.ShipQty) END AS QTD_SHIP_QTY, CASE WHEN SUM(V_QTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.ShipQty) END AS QTDLY_SHIP_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.ShipQty) END AS YTD_SHIP_QTY, CASE WHEN SUM(V_YTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.ShipQty) END AS YTDLY_SHIP_QTY,
CASE WHEN SUM(V_LW_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.NetShip$) END AS LW_SHIP_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetShip$) END AS LWLY_SHIP_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetShip$) END AS MTD_SHIP_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetShip$) END AS MTDLY_SHIP_AMT,
CASE WHEN SUM(V_QTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetShip$) END AS QTD_SHIP_AMT, CASE WHEN SUM(V_QTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetShip$) END AS QTDLY_SHIP_AMT,
CASE WHEN SUM(V_YTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetShip$) END AS YTD_SHIP_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetShip$) END AS YTDLY_SHIP_AMT,
--ADDING NET DEMAND AMOUNTS (Demand Value after discount minus returns)
--test of new lw net:
--sum(V_LW_SKU_SLS.netamt-v_lw_returns.subtotal) as NewLWNet$,
CASE WHEN SUM(V_LW_SKU_SLS.NetAmt)-sum(V_LW_RETURNS.subtotal) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.NetAmt)-sum(V_LW_RETURNS.subtotal) END AS LW_NET_DEMAND_AMT,
CASE WHEN SUM(V_LWLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetAmt) END AS LWLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetAmt) END AS MTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_MTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetAmt) END AS MTDLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_QTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetAmt) END AS QTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetAmt) END AS QTDLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_YTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetAmt) END AS YTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_YTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetAmt) END AS YTDLY_NET_DEMAND_AMT,
--ADDING NET DEMAND QTY
CASE WHEN SUM(V_LW_SKU_SLS.UnitsSold)-sum(V_LW_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.UnitsSold)-sum(V_LW_RETURNS.quantity) END AS LW_NET_DEMAND_QTY,
CASE WHEN SUM(V_LWLY_SKU_SLS.UnitsSold-V_LWLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.UnitsSold-V_LWLY_RETURNS.quantity) END AS LWLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.UnitsSold-V_MTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.UnitsSold-V_MTD_RETURNS.quantity) END AS MTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_MTDLY_SKU_SLS.UnitsSold-V_MTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.UnitsSold-V_MTDLY_RETURNS.quantity) END AS MTDLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.UnitsSold-V_QTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.UnitsSold-V_QTD_RETURNS.quantity) END AS QTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_QTDLY_SKU_SLS.UnitsSold-V_QTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.UnitsSold-V_QTDLY_RETURNS.quantity) END AS QTDLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.UnitsSold-V_YTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.UnitsSold-V_YTD_RETURNS.quantity) END AS YTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.UnitsSold-V_YTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.UnitsSold-V_YTDLY_RETURNS.quantity) END AS YTDLY_NET_DEMAND_QTY,
--Return Metrics
CASE WHEN SUM(CAST(dbo.V_LW_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LW_RETURNS.quantity AS INT)) END AS LW_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_LW_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_LW_RETURNS.subtotal AS INT)) END AS LW_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_LWLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LWLY_RETURNS.quantity AS INT)) END AS LWLY_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_LWLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LWLY_RETURNS.subtotal AS INT)) END AS LWLY_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_MTD_RETURNS.quantity AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_MTD_RETURNS.quantity AS INT)) END AS MTD_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_MTD_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_MTD_RETURNS.subtotal AS INT)) END AS MTD_RETURN_AMT,
CASE WHEN SUM(CAST(dbo.V_MTDLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_MTDLY_RETURNS.quantity AS INT)) END AS MTDLY_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_MTDLY_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_MTDLY_RETURNS.subtotal AS INT)) END AS MTDLY_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_QTD_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTD_RETURNS.quantity AS INT)) END AS QTD_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_QTD_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTD_RETURNS.subtotal AS INT)) END AS QTD_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_QTDLY_RETURNS.quantity AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_QTDLY_RETURNS.quantity AS INT)) END AS QTDLY_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_QTDLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTDLY_RETURNS.subtotal AS INT)) END AS QTDLY_RETURN_AMT,
CASE WHEN SUM(CAST(dbo.V_YTD_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTD_RETURNS.quantity AS INT)) END AS YTD_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_YTD_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_YTD_RETURNS.subtotal AS INT)) END AS YTD_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_YTDLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTDLY_RETURNS.quantity AS INT)) END AS YTDLY_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_YTDLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTDLY_RETURNS.subtotal AS INT)) END AS YTDLY_RETURN_AMT
FROM dbo.D_PRODUCTS LEFT OUTER JOIN
dbo.V_YTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_YTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_QTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_QTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_MTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LWLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_LWLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_MTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LW_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_LW_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LW_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_LW_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_MTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_LWLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_LWLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_MTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTDLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_QTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_YTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_QTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTDLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_YTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTDLY_SKU_SLS.ITEMVARIANTID
group by [Group ID]
Upvotes: 2
Views: 45
Answers (1)
SteveC
Reputation: 6015
For both the LW_NET_DEMAND_AMT column and the LW_NET_DEMAND_QTY columns I added ISNULL() functions inside each SUM() function. Does this produce the expected results?
SELECT [Group ID]
SUM(dbo.D_PRODUCTS.OH_QTY) AS OnHandQty, CASE WHEN SUM(V_LW_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.UnitsSold) END AS LW_SALE_QTY,
CASE WHEN SUM(V_LW_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.NetAmt) END AS LW_SALE_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.UnitsSold) END AS LWLY_SALE_QTY,
CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetAmt) END AS LWLY_SALE_AMT, CASE WHEN SUM(V_MTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.UnitsSold) END AS MTD_SALE_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetAmt) END AS MTD_SALE_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.UnitsSold) END AS MTDLY_SALE_QTY,
CASE WHEN SUM(V_MTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetAmt) END AS MTDLY_SALE_AMT, CASE WHEN SUM(V_QTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.UnitsSold) END AS QTD_SALE_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetAmt) END AS QTD_SALE_AMT, CASE WHEN SUM(V_QTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.UnitsSold) END AS QTDLY_SALE_QTY,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetAmt) END AS QTDLY_SALE_AMT, CASE WHEN SUM(V_YTD_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.UnitsSold) END AS YTD_SALE_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetAmt) END AS YTD_SALE_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.UnitsSold) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.UnitsSold) END AS YTDLY_SALE_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.NetAmt) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetAmt) END AS YTDLY_SALE_AMT, CASE WHEN SUM(V_LW_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.ShipQty - v_lw_returns.quantity)
END AS LW_NET_SHIP_QTY, CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.ShipQty - v_lwly_returns.quantity) END AS LWLY_NET_SHIP_QTY, CASE WHEN SUM(V_MTD_SKU_SLS.ShipQty) IS NULL
THEN 0 ELSE SUM(V_MTD_SKU_SLS.ShipQty - v_mtd_returns.quantity) END AS MTD_NET_SHIP_QTY, CASE WHEN SUM(V_MTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.ShipQty - v_mtdly_returns.quantity) END AS MTDLY_NET_SHIP_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.ShipQty - v_qtd_returns.quantity) END AS QTD_NET_SHIP_QTY, CASE WHEN SUM(V_QTDLY_SKU_SLS.ShipQty) IS NULL
THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.ShipQty - v_qtdly_returns.quantity) END AS QTDLY_NET_SHIP_QTY, CASE WHEN SUM(V_YTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.ShipQty - v_ytd_returns.quantity) END AS YTD_NET_SHIP_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.ShipQty - v_ytdly_returns.quantity) END AS YTDLY_NET_SHIP_QTY, CASE WHEN SUM(V_LW_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_LW_SKU_SLS.NetShip$ - v_lw_returns.subtotal) END AS LW_NET_SHIP_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetShip$ - v_lwly_returns.subtotal) END AS LWLY_NET_SHIP_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetShip$ - v_mtd_returns.subtotal) END AS MTD_NET_SHIP_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetShip$ - v_mtdly_returns.subtotal) END AS MTDLY_NET_SHIP_AMT, CASE WHEN SUM(V_QTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetShip$ - v_qtd_returns.subtotal) END AS QTD_NET_SHIP_AMT,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetShip$ - v_qtdly_returns.subtotal) END AS QTDLY_NET_SHIP_AMT, CASE WHEN SUM(V_YTD_SKU_SLS.NetShip$) IS NULL
THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetShip$ - v_ytd_returns.subtotal) END AS YTD_NET_SHIP_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetShip$ - v_ytdly_returns.subtotal) END AS YTDLY_NET_SHIP_AMT,
CASE WHEN SUM(V_LW_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.ShipQty) END AS LW_SHIP_QTY, CASE WHEN SUM(V_LWLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.ShipQty) END AS LWLY_SHIP_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.ShipQty) END AS MTD_SHIP_QTY, CASE WHEN SUM(V_MTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.ShipQty) END AS MTDLY_SHIP_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.ShipQty) END AS QTD_SHIP_QTY, CASE WHEN SUM(V_QTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.ShipQty) END AS QTDLY_SHIP_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.ShipQty) END AS YTD_SHIP_QTY, CASE WHEN SUM(V_YTDLY_SKU_SLS.ShipQty) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.ShipQty) END AS YTDLY_SHIP_QTY,
CASE WHEN SUM(V_LW_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LW_SKU_SLS.NetShip$) END AS LW_SHIP_AMT, CASE WHEN SUM(V_LWLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetShip$) END AS LWLY_SHIP_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetShip$) END AS MTD_SHIP_AMT, CASE WHEN SUM(V_MTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetShip$) END AS MTDLY_SHIP_AMT,
CASE WHEN SUM(V_QTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetShip$) END AS QTD_SHIP_AMT, CASE WHEN SUM(V_QTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetShip$) END AS QTDLY_SHIP_AMT,
CASE WHEN SUM(V_YTD_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetShip$) END AS YTD_SHIP_AMT, CASE WHEN SUM(V_YTDLY_SKU_SLS.NetShip$) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetShip$) END AS YTDLY_SHIP_AMT,
--ADDING NET DEMAND AMOUNTS (Demand Value after discount minus returns)
--test of new lw net:
--sum(V_LW_SKU_SLS.netamt-v_lw_returns.subtotal) as NewLWNet$,
SUM(ISNULL(V_LW_SKU_SLS.NetAmt, 0))-sum(ISNULL(V_LW_RETURNS.subtotal, 0)) AS LW_NET_DEMAND_AMT,
CASE WHEN SUM(V_LWLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.NetAmt) END AS LWLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_MTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.NetAmt) END AS MTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_MTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.NetAmt) END AS MTDLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_QTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.NetAmt) END AS QTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_QTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.NetAmt) END AS QTDLY_NET_DEMAND_AMT,
CASE WHEN SUM(V_YTD_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.NetAmt) END AS YTD_NET_DEMAND_AMT,
CASE WHEN SUM(V_YTDLY_SKU_SLS.NetAMT) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.NetAmt) END AS YTDLY_NET_DEMAND_AMT,
--ADDING NET DEMAND QTY
SUM(ISNULL(V_LW_SKU_SLS.UnitsSold, 0))-sum(ISNULL(V_LW_RETURNS.quantity, 0_)) AS LW_NET_DEMAND_QTY,
CASE WHEN SUM(V_LWLY_SKU_SLS.UnitsSold-V_LWLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_LWLY_SKU_SLS.UnitsSold-V_LWLY_RETURNS.quantity) END AS LWLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_MTD_SKU_SLS.UnitsSold-V_MTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_MTD_SKU_SLS.UnitsSold-V_MTD_RETURNS.quantity) END AS MTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_MTDLY_SKU_SLS.UnitsSold-V_MTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_MTDLY_SKU_SLS.UnitsSold-V_MTDLY_RETURNS.quantity) END AS MTDLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_QTD_SKU_SLS.UnitsSold-V_QTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_QTD_SKU_SLS.UnitsSold-V_QTD_RETURNS.quantity) END AS QTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_QTDLY_SKU_SLS.UnitsSold-V_QTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_QTDLY_SKU_SLS.UnitsSold-V_QTDLY_RETURNS.quantity) END AS QTDLY_NET_DEMAND_QTY,
CASE WHEN SUM(V_YTD_SKU_SLS.UnitsSold-V_YTD_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_YTD_SKU_SLS.UnitsSold-V_YTD_RETURNS.quantity) END AS YTD_NET_DEMAND_QTY,
CASE WHEN SUM(V_YTDLY_SKU_SLS.UnitsSold-V_YTDLY_RETURNS.quantity) IS NULL THEN 0 ELSE SUM(V_YTDLY_SKU_SLS.UnitsSold-V_YTDLY_RETURNS.quantity) END AS YTDLY_NET_DEMAND_QTY,
--Return Metrics
CASE WHEN SUM(CAST(dbo.V_LW_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LW_RETURNS.quantity AS INT)) END AS LW_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_LW_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_LW_RETURNS.subtotal AS INT)) END AS LW_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_LWLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LWLY_RETURNS.quantity AS INT)) END AS LWLY_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_LWLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_LWLY_RETURNS.subtotal AS INT)) END AS LWLY_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_MTD_RETURNS.quantity AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_MTD_RETURNS.quantity AS INT)) END AS MTD_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_MTD_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_MTD_RETURNS.subtotal AS INT)) END AS MTD_RETURN_AMT,
CASE WHEN SUM(CAST(dbo.V_MTDLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_MTDLY_RETURNS.quantity AS INT)) END AS MTDLY_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_MTDLY_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_MTDLY_RETURNS.subtotal AS INT)) END AS MTDLY_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_QTD_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTD_RETURNS.quantity AS INT)) END AS QTD_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_QTD_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTD_RETURNS.subtotal AS INT)) END AS QTD_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_QTDLY_RETURNS.quantity AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_QTDLY_RETURNS.quantity AS INT)) END AS QTDLY_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_QTDLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_QTDLY_RETURNS.subtotal AS INT)) END AS QTDLY_RETURN_AMT,
CASE WHEN SUM(CAST(dbo.V_YTD_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTD_RETURNS.quantity AS INT)) END AS YTD_RETURN_QTY, CASE WHEN SUM(CAST(dbo.V_YTD_RETURNS.subtotal AS INT)) IS NULL
THEN 0 ELSE SUM(CAST(dbo.V_YTD_RETURNS.subtotal AS INT)) END AS YTD_RETURN_AMT, CASE WHEN SUM(CAST(dbo.V_YTDLY_RETURNS.quantity AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTDLY_RETURNS.quantity AS INT)) END AS YTDLY_RETURN_QTY,
CASE WHEN SUM(CAST(dbo.V_YTDLY_RETURNS.subtotal AS INT)) IS NULL THEN 0 ELSE SUM(CAST(dbo.V_YTDLY_RETURNS.subtotal AS INT)) END AS YTDLY_RETURN_AMT
FROM dbo.D_PRODUCTS LEFT OUTER JOIN
dbo.V_YTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_YTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_QTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_QTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_MTDLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTDLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LWLY_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_LWLY_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_MTD_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTD_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LW_RETURNS ON dbo.D_PRODUCTS.VariantID = dbo.V_LW_RETURNS.variant_id LEFT OUTER JOIN
dbo.V_LW_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_LW_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_MTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_LWLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_LWLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_MTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_MTDLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_QTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_YTD_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTD_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_QTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_QTDLY_SKU_SLS.ITEMVARIANTID LEFT OUTER JOIN
dbo.V_YTDLY_SKU_SLS ON dbo.D_PRODUCTS.VariantID = dbo.V_YTDLY_SKU_SLS.ITEMVARIANTID
group by [Group ID]
Upvotes: 0
Related Questions
- Total sum value out of Group By
- Sum functions doesn't show total values in single row after grouping
- Why this SUM() field not working as expected?
- SQL: Sum / Group By Issue for Multiple Rows
- SUM with GROUP BY don't display zero sums
- SQL group and sum
- SQL sum after group by
- SQL GROUP BY with a SUM CASE statement
- SQL Query CASE with SUM and Grouping
- How to add up column data values when using GROUP BY