locked
Not sure why this doesn't work.... RRS feed

  • Question

  • I'm attempting to compute a Net profit within my sql by calculating first the Expense, then the Revenue, and then subtracting the two columns.

    The following is the code that does NOT work:

    Select
    Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,(ContRevenue - Expense) as NetProfit

    from GLdetail

    group by Left(Task, 5)

    The error for both 'ContRevenue' and 'Expense' is 'invalid column name'. This error occurs in the parenthesis where I attempt to subtract expense from ContRevenue. If I take that portion out, it works fine. If I replace those column names with the actual calculation, i.e. sum(x) - sum(x), it also works fine.

    I'm curious as to why this doesn't work?

    TIA

     

     

    Monday, March 26, 2007 3:23 PM

Answers

  • You are assigning an ALIAS to the product of [ sum(Amount*1.3) ] and the product of [ sum(Amount*.029) ], and then attempting to use those products in the NetProfit calculation.

    Those calculations are not complete and available at that moment.

    I suggest that you might first calculate the products that are assigned to ALIASes, and then calculate the NetProfit. Perhaps something like (untested):

    SELECT
       dt.Project,
       dt.Expense,
       dt.ContRevenue,
       NetProfit = ( dt.ContRevenue - dt.Expense)
    FROM (SELECT
             Project     = left(Task, 5),
             Expense     = sum(Amount*0.29),
             ContRevenue = sum(Amount*1.3)
          FROM GLdetail
          GROUP BY left(Task, 5)
         ) dt
    ORDER BY Project

    Monday, March 26, 2007 3:53 PM
  • If you replace the Aliases with the actual calculations, it should work fine:

    Select Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,((Sum(Amount*1.3) ) - (Sum(Amount*1.3))) as NetProfit

    from GLdetail

    group by Left(Task, 5)

    As Arnie has said, the Aliases are not assigned a value until after the query runs, so the calculations are null. If you assigned the calculations to an alias in a subquery, the subquery would run first, and at that point you could reference just the Alias.

    However, in the example you've provided, and for all intents and purposes, you could simply use the above query.

    Adamus

    Monday, March 26, 2007 4:13 PM

All replies

  • You are assigning an ALIAS to the product of [ sum(Amount*1.3) ] and the product of [ sum(Amount*.029) ], and then attempting to use those products in the NetProfit calculation.

    Those calculations are not complete and available at that moment.

    I suggest that you might first calculate the products that are assigned to ALIASes, and then calculate the NetProfit. Perhaps something like (untested):

    SELECT
       dt.Project,
       dt.Expense,
       dt.ContRevenue,
       NetProfit = ( dt.ContRevenue - dt.Expense)
    FROM (SELECT
             Project     = left(Task, 5),
             Expense     = sum(Amount*0.29),
             ContRevenue = sum(Amount*1.3)
          FROM GLdetail
          GROUP BY left(Task, 5)
         ) dt
    ORDER BY Project

    Monday, March 26, 2007 3:53 PM
  • If you replace the Aliases with the actual calculations, it should work fine:

    Select Left(Task, 5) as Project, Sum(Amount*0.29) as Expense, Sum(Amount*1.3) as ContRevenue,((Sum(Amount*1.3) ) - (Sum(Amount*1.3))) as NetProfit

    from GLdetail

    group by Left(Task, 5)

    As Arnie has said, the Aliases are not assigned a value until after the query runs, so the calculations are null. If you assigned the calculations to an alias in a subquery, the subquery would run first, and at that point you could reference just the Alias.

    However, in the example you've provided, and for all intents and purposes, you could simply use the above query.

    Adamus

    Monday, March 26, 2007 4:13 PM
  • The following query work for you..

    Select
     Left(Task, 5) as Project,
     Sum(Amount*0.29) as Expense,  
     Sum(Amount*1.3) as ContRevenue,
     (Sum(Amount*1.3) - Sum(Amount*0.29)) as NetProfit
    from  
     GLdetail
    group by
     Left(Task, 5)

    Monday, March 26, 2007 5:43 PM