a subquery within a subquery - is it possible?

Question

Hi All,

I have this sql below that i use to compare values of z. I put z in a subquery and then compare it.

My qn is in in my else statement below, i want to put in another formula to calculate something else of which the info is only available in another table called var1(for example, n). i would like to put in sum(n)/count(n) to end as cv in my else statement. and then make a case statement if the cv > 10 then too large, if < 10 too little.

am i supposed to do another subquery in this subquery? any advise pls. thanks!

select 
z, 
CASE 
when z > 1 then 'Pls increase your inputs' 
when z < -1 then 'Pls decrease your inputs'
else 'No comment' END as Input

from 

(select
    case
    when S < 0 then (S/En-Eg))
    else (S/En+Eg)) 
    end as z
from var2);

Answer 1

Hi Rayhan,

You are on the right track. For aggregates like SUM() and COUNT(), I suggest using a correlated subquery; that is, the subquery is guaranteed to return one aggregate value (SUM(n)/COUNT(n)) for each key value (var1.primary_key).

For example, this query uses a correlated subquery to return the value you desire, making SUM(n)/COUNT(n) available to the outer query:

SELECT      v.z,
            CASE WHEN v.z > 1
                 THEN 'Pls increase your inputs'
                 WHEN v.z < -1
                 THEN 'Pls decrease your inputs'
                 ELSE 'No comment' END AS INPUT,
            CASE WHEN v.cv > 10
                 THEN 'Pls decrease your inputs'
                 WHEN v.cv < 10
                 THEN 'Pls increase your inputs'
                 ELSE 'No comment' END AS INPUT                     
  FROM      (SELECT      CASE WHEN s < 0
                              THEN (s / en - eg)
                              ELSE (S/En+Eg) END AS 'z',
                         ISNULL(AVG_N.cv, 0) AS 'cv'
               FROM      var2
                         INNER JOIN 
                         (
                          SELECT      var1.primary_key AS 'PrimaryKey',
                                      SUM(var1.N) / COUNT(var1.N) AS 'cv'
                            FROM      var1
                           WHERE      var1.primary_key = var2.primary_key
                          GROUP BY    var1.N
                         ) AVG_N
                         ON AVG_N.PrimaryKey = var2.primary_key
            ) v

Answer 2

hi schultkl,

thanks for your reply. i will be trying this out very soon and let you know...thanks a lot!