How to Calculate Percentage of Revenue based on aggregate column Total sales?

Question

I am trying to add a column that calculate the Percentage of total revenue and I am stuck with the following error:

Error: Msg 207, Level 16, State 1, Line 14 Invalid column name 'Customerkey'.

In that line I’m trying to join Table 1 and Table 3 but MS SQL Server won’t recognize T.Customerkey even though customerkey exists in the dbo.FactInternetSales table. Also, when I add T.Grand_Tot_Rev in my Group By clause, it returns 0.04 for every row. I know it's wrong because I do not want T.Grand_Tot_Rev to be part of the aggregate, because it should remain constant for every record. How can I achieve that I am looking for? Thank you in advance. By the way, I am using the AdventureWorksDW2012 database.

SELECT fs.CustomerKey , 
       M.Total_sales , 
       M.Total_cost , 
       M.Total_sales - M.Total_cost AS Total_Margin , 
       T.Grand_Tot_Rev( M.Total_sales / T.Grand_Tot_Rev ) * 100 AS Prct_Total_Revenue
  FROM dbo.FactInternetSales fs , -- Table 1 --

( 
  SELECT customerkey , 
         SUM( SalesAmount )AS Total_Sales , 
         SUM( TotalProductCost )Total_cost
    FROM dbo.FactInternetSales
    GROUP BY customerkey 
) M , --Table 2 --

( 
  SELECT SUM( SalesAmount )AS Grand_Tot_Rev
    FROM dbo.FactInternetSales 
) T --Table 3 --

  WHERE fs.CustomerKey =  M.CustomerKey -- Join 1 --     
    AND M.CustomerKey = T.Customerkey   -- Join 2 -- 
  GROUP BY fs.CustomerKey , 
           M.Total_sales , 
           M.Total_cost , 
           T.Grand_Tot_Rev
  ORDER BY 2 DESC;

Answer 1

If you want the T.Grand_Tot_Rev as a constant over all rows try removing the second join AND M.CustomerKey = T.Customerkey -- Join 2 -- so the query looks like this:

SELECT fs.CustomerKey , 
       M.Total_sales , 
       M.Total_cost , 
       M.Total_sales - M.Total_cost AS Total_Margin , 
       T.Grand_Tot_Rev,
      ( M.Total_sales / T.Grand_Tot_Rev ) * 100 AS Prct_Total_Revenue
  FROM dbo.FactInternetSales fs , -- Table 1 --

( 
  SELECT customerkey , 
         SUM( SalesAmount )AS Total_Sales , 
         SUM( TotalProductCost )Total_cost
    FROM dbo.FactInternetSales
    GROUP BY customerkey 
) M , --Table 2 --

( 
  SELECT SUM( SalesAmount )AS Grand_Tot_Rev
    FROM dbo.FactInternetSales
) T --Table 3 --

  WHERE fs.CustomerKey =  M.CustomerKey -- Join 1 --     
    --AND M.CustomerKey = T.Customerkey    -- Join 2 -- 
  GROUP BY fs.CustomerKey , 
           M.Total_sales , 
           M.Total_cost , 
           T.Grand_Tot_Rev
  ORDER BY 2 DESC;

Another way to write the same query that is a bit more compact and might have slightly better performance:

;WITH 
    T AS (
        SELECT SUM(SalesAmount) AS Grand_Tot_Rev  
        FROM dbo.FactInternetSales
    ),
    M AS (
       SELECT customerkey , 
        SUM(SalesAmount) AS Total_Sales , 
        SUM(TotalProductCost) AS Total_cost
       FROM dbo.FactInternetSales
       GROUP BY CustomerKey
)

SELECT 
    customerkey , 
    Total_Sales , 
    Total_cost,
    Total_Sales - Total_cost AS Total_Margin , 
    Grand_Tot_Rev,
    Total_Sales / Grand_Tot_Rev * 100 AS Prct_Total_Revenue   
FROM M, T
ORDER BY 2 DESC;

To see the really small values you can force a conversion to a wider data type:

;WITH 
    T AS (
        SELECT CAST(SUM(SalesAmount) AS decimal) AS Grand_Tot_Rev  
        FROM dbo.FactInternetSales
    ),
    M AS (
       SELECT customerkey , 
        CAST(SUM(SalesAmount) AS decimal(15,10)) AS Total_Sales , 
        CAST(SUM(TotalProductCost) AS decimal(15,10)) AS Total_cost
       FROM dbo.FactInternetSales
       GROUP BY CustomerKey
)

SELECT 
    customerkey , 
    Total_Sales , 
    Total_cost,
    Total_Sales - Total_cost AS Total_Margin , 
    Grand_Tot_Rev,
    Total_Sales / Grand_Tot_Rev * 100 AS Prct_Total_Revenue   
FROM M, T
ORDER BY 2 DESC;