Question :
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
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;