Add a number value to column in SQL query using SELECT method-CodePudding

I have am working on adding a query that calculates tuition costs. It should do this by using the Tuition table which only includes the FullTimeCost (a static number for the student fees), and the PerUnitCost (the cost per credit hour).

I am trying to use a SELECT to return 3 more columns, 1 constant value of 12 called units, and 2 more that calculate the rest based on simple math.

The problem I am having is that I cannot seem to make the column Units have a default value of 12.

This is my code, and the issue I am having is that when I use this approach, the following formulas do not recognize the the columns being created in the previous lines.

All I need is for the 3rd Line to recognize Units so it can multiply by 12 as intended. Also this is for school, so a comment saying just change it to 12 is not useful.

SELECT 
    FullTimeCost, PerUnitCost,
    12 AS Units,
    PerUnitCost * Units AS TotalPerUnitCost,
    FullTimeCost   TotalPerUnitCost AS TotalTuition
FROM
    Tuition

CodePudding user response：

You cannot re-use a column alias in the select. However, SQL Server gives you a convenient way to define the alias in the from clause, so you can use it:

SELECT t.FullTimeCost, t.PerUnitCost, v.Units,
       v2.TotalPerUnitCost,
       (t.FullTimeCost   v2.TotalPerUnitCost AS TotalTuition
FROM Tuition t CROSS APPLY
     (VALUES (12)) v(units) CROSS APPLY
     (VALUES (t.PerUnitCost * v.Units)) v2(TotalPerUnitCost);

CodePudding user response：

Use a CTE to "add" your constant as a column and then apply the calculation. Without context, a variable would also be just as simple and useful.

with cte as (select FullTimeCost, PerUnitCost, 12 as Units 
    from dbo.Tuition
   )
SELECT 
    FullTimeCost, PerUnitCost,
    Units,
    PerUnitCost * Units AS TotalPerUnitCost,
    FullTimeCost   TotalPerUnitCost AS TotalTuition
FROM cte
order by ...;

There are, of course, other ways to accomplish this. Not certain what your coursework has covered but I assume that recent topics should have provided techniques to do this.

CodePudding user response：

Using apply as shown by Gordon's answer is the most elegant solution and also noted in the comments is another way using a derived table.

As you have no doubt gathered, the problem is that during query compilation, the optimizer does not "see" the calculated column aliases as it can only (generally) access columns available from tables in the where clause, or as shown by Gordon, using an apply().

What you can also do is use a derived table, by first selecting the columns you need from your table and also adding your additional columns.

You then wrap this in parentheses - it's now a derived table ie, the results of the parenthesis content is itself a table available to an outer select.

You then use this as the source for an outer select which has visiblity of any additional columns you have added.

A complication with your query is that you want to add a constant value Units and then reference it, and also reference a second calculated column that makes use of Units.

I would simply use a single derived table to calculate the TotalPerUnitCost, you don't need Units since it's used only once.

select 
    FullTimeCost, PerUnitCost, TotalPerUnitCost,
    FullTimeCost   TotalPerUnitCost as TotalTuition
from (
    select FullTimeCost, PerUnitCost, TotalPerUnitCost, PerUnitCost * 12 as TotalPerUnitCost
    from Tuition
)t

CodePudding user response：

A simple solution could be to repeat the necessary Units value and calculation in the SELECT list. Something like this

SELECT FullTimeCost, PerUnitCost, 12 AS Units,
       PerUnitCost * 12 AS TotalPerUnitCost,
       FullTimeCost   (PerUnitCost * 12) AS TotalTuition
FROM Tuition t;