Display subquery result and use it for calculation

Question

Display subquery result and use it for calculation

Navigation

#1 by (4 votes)

3

I need to display a result of a subquery and use the same result to perform a calculation, is it possible to play it to a variable in MS SQL 2008 or something along those lines? example:

SELECT
    @test = (SELECT COUNT(*) FROM [tableTest] WHERE [tableTest].[columnA] = [tableA].[columnA]) as 'Counter'
    , (@test * 50) as 'Calc'
    , [tableA].[columnA]
FROM tableA

sql sql-server sql-server-2008

asked by anonymous 05.01.2015 / 17:24

1 answer

How to find conflicting files in GIT? "Transform" one element into another with JQuery

score 4 · Accepted Answer

You can use a CTE (Common Table Expression)

WITH CounterTable(columnA, Counter) AS
( 
    SELECT [tableTest].[columnA], COUNT(*) FROM [tableTest] 
    WHERE [tableTest].[columnA] = [tableA].[columnA]
) 
SELECT Counter, columnA, Counter * 50 AS Calc
FROM CounterTable;

Using INNER JOIN can make your query more readable

WITH CounterTable(columnA, Counter) AS
( 
    SELECT [tableTest].[columnA], COUNT(*) 
    FROM [tableTest] 
    INNER JOIN [tableA] ON [tableTest].[columnA] = [tableA].[columnA]
) 
SELECT Counter, columnA, Counter * 50 AS Calc
FROM CounterTable;