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

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

CodePudding user response：

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

CodePudding user response：

Make nested query. So instead of two queries, do one query where "Previous Query just run" is replaced with first query

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)