How to run two queries with the second query using the results from the first

Question

I have tried looking this up, but i am not sure if my terminology is correctly. My initial query works fine, however i want to do a calculation based on the results from the first query. Its not possible to do this from the initial query due to the Inner Joins and Group By

I can accomplish this by saving this Query as a View and running my second query against the view. But this is not ideal for the usage.

What is the correct wording for using two queries in such a way, the second using the results from the first.

Example:

Select, column1, column2, column3, column4, SUM(column5) as column5, SUM(column6) as column6, TableB.column7
From TableA 
Left Out Join TableB 
on TableB.column7 = TableA.column1
Group By column1, column2, column3, column4

Select column1, column2, column3, column4, column5, column6,
(ISNULL (NULLIF(column5 - column6,0) / NULLIF(column5,0),0)  * 100)  else 0 end as columnGP
from (Previous Query just ran) 

How can i store the results as a Variable, how long is the Data held as a variable? I storing as a variable will have a knock on effect from a performance side

Answer 1

You can use the first query as a sub-query in the second one

Select
    column1, column2, column3, column4, column5, column6,
    (ISNULL (NULLIF(column5 - column6,0) / NULLIF(column5,0),0)  * 100)  else 0 end as columnGP
from (
    Select, column1, column2, column3, column4, SUM(column5) as column5, SUM(column6) as column6, TableB.column7
    From TableA 
    Left Out Join TableB 
    on TableB.column7 = TableA.column1
    Group By column1, column2, column3, column4
) x

To do this enclose the sub-query in braces and give it an alias acting as a table name (here x).

I just copied your queries; however, there seems to be a syntax error in them. else 0 end looks like the end of a missing case-expression.

But you can do the calculation in a single query

Select
    column1, column2, column3, column4,
    SUM(column5) as sum_column5, SUM(column6) as sum_column6,
    TableB.column7,
    CASE WHEN SUM(column5) = 0
      THEN 0
      ELSE (SUM(column5) - SUM(column6)) / SUM(column5) * 100
    END AS columnGP
From
    TableA
    Left Outer Join TableB
        on TableB.column7 = TableA.column1
Group By
    column1, column2, column3, column4, TableB.column7