SQL Server : multiply column with alias name in select list

Question

I have a select list in my query like this:

SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
    MIN(t.Title) AS Title,
    t.ItemID, ' + @selectedColumn + ' as SelectedColumnSales ' + ', 
    t.CurrentPrice,
    (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem
FROM 
    dbo.SearchedUserItems t

The part of the query that I'm having problem with is the following:

  (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem

In the select list... The "SelectedColumnSales" can be a different column based on what I pass into the query like following:

DECLARE @selectedColumn NVARCHAR(500)

    IF(@SelectedRange=7)
       SET @selectedColumn = 't.SevenDaySales'
    ELSE IF (@SelectedRange=14)
       SET @selectedColumn='t.FourteenDaySales'
    ELSE IF (@SelectedRange=21)
       SET @selectedColumn='t.TwentyOneDaySales'
    ELSE IF (@SelectedRange=30)
       SET @selectedColumn='t.ThirtyDaySales'

Now to get revenue per item column I need to multiply these two like above, but the query throws this error:

Inner exception: SqlException: Invalid column name 'SelectedColumnSales'.

How can I multiply the dynamic column with the static column in the select list?

Can someone help me out?

Wrap your query up in a derived table, then you can reference those columns outside it. — jarlh, Aug 24 '18 at 14:02

score 1 · Accepted Answer · answered Aug 24 '18 at 14:09

1

if you're writing a dynamic query, which it looks like you are, you can just put the variable in your equation

 declare @sql nvarchar(max)
 set @sql = 'select 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
         , (t.CurrentPrice * ' + @selectedColumn + ') as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t'
exec(@sql)

answered Aug 24 '18 at 14:09

JamieD77

13,796
1
17
27

I would put `QUOTENAME()` around the column name just to add a little extra safety against SQL injection. And for best practices / continuity I would always use `sp_executesql` even when you don't have strongly-typed parameters to pass. – Aaron Bertrand Aug 24 '18 at 14:36

John Cappelletti · Answer 2 · 2018-08-24T14:22:26.393

Perhaps pass the INDEX of the user's selection and use CHOOSE()

Let's assume:

Item               Index
SevenDaySales      0
FourteenDaySales   1
TwentyOneDaySales  2
ThirtyDaySales     3

Then you can

SELECT COUNT(*) OVER () AS TotalRowsFound
      ,MIN(t.Title) AS Title
      ,t.ItemID
      ,choose(@Index+1,t.SevenDaySales,t.FourteenDaySales,t.TwentyOneDaySales,t.ThirtyDaySales) as SelectedColumnSales  
      ,t.CurrentPrice
      ,(t.CurrentPrice * choose(@Index+1,t.SevenDaySales,t.FourteenDaySales,t.TwentyOneDaySales,t.ThirtyDaySales)) as TotalRevenuePerItem
 FROM  dbo.SearchedUserItems t

If you don't want to pass the index, rather then @Index+1 , you can @SelectedRange/7

score 0 · Answer 3 · answered Aug 24 '18 at 14:05

Instead of a string concat you could try using direcly the login in query using a case when clause

 SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        , case when @SelectedRange=7 then t.SevenDaySales 
              when @SelectedRange=14 then t.FourteenDaySales
              when @SelectedRange=21 then t.TwentyOneDaySales
              when (@SelectedRange=30 then t.ThirtyDaySales end 
         as SelectedColumnSales  
        , t.CurrentPrice
         , (t.CurrentPrice * t.SelectedColumnSales) as TotalRevenuePerItem

    FROM 
        dbo.SearchedUserItems t

score 0 · Answer 4 · answered Aug 24 '18 at 14:05

Wrap your query up in a derived table, then you can reference those columns outside it:

select dt.TotalRowsFound, dt.Title, ... ,
      (dt.CurrentPrice * dt.SelectedColumnSales) as TotalRevenuePerItem
from
(
  SELECT 
    COUNT(*) OVER () AS TotalRowsFound,
        MIN(t.Title) AS Title
        , t.ItemID
        ,' + @selectedColumn + ' as SelectedColumnSales ' + 
        ', t.CurrentPrice
    FROM 
        dbo.SearchedUserItems t
) dt

SQL Server : multiply column with alias name in select list

4 Answers4