CODEWITHSUNDEEP
sqlsql-serverstored-proceduressubquery
Newbie
Newbie

Reputation: 63

Perform subquery as two columns

I have search for the similar question but I haven't found any. There are two almost similar question, but the solution is using the JOIN because he/she was asking for different solution, but mine one is different. I have to use sub-query for Expression from the same table with parent query.

Is it possible for me to perform subquery like this?

Select summ.* 
from
    (select 
         t.column1 as 'field_1',
         t.column2 as 'field_2',
         (select
              Sum(u.col1) as 'field_3',
              Sum(u.col2) as 'field_4' 
          from 
              table1 u 
          where 
              u.col3 = t.column1) as 'field_3', 'field_4'),
         t.column5 as 'field_5'
     from 
         table1.t) summ

I need this because, if I get field_3 and field_4 from a separate subquery, SQL Server will take long time to execute the stored procedure.

Currently, the query like this (note that SQL Server needs to subquery twice for field_3 and field_4):

Select summ.* 
from
    (select 
         t.column1 as 'field_1',
         t.column2 as 'field_2',
         (select Sum(u.col1) 
          from table1 u 
          where u.col3 = t.column1) as field_3,
         (select Sum(u.col2) 
          from table1 u 
          where u.col3 = t.column1) as field_4,
         t.column5 as 'field_5'
     from 
         table1.t) summ

If that possible, or is there any kind of syntax I can use to accomplish the same thing

I have updated the above (expected) and below (my current) query.

Upvotes: 0

Views: 64

Answers (3)

Sergey Menshov
Sergey Menshov

Reputation: 3906

I think you can use OUTER APPLY or CROSS APPLY

select t.column1 as field_1,
       t.column2 as field_2,
       q.field_3,
       q.field_4,
       t.column5 as field_5
from table1 t
outer apply
  (
    select
      SUM(u.col1) field_3,
      SUM(u.col2) field_4
    from  table1 u
    where u.col3 = t.column1
  ) q

You can use external parameters in the APPLY subquery. The subquery executes for each rows from table1 t.

And I think you can use the following variant too

SELECT t.column1 as field_1,
       t.column2 as field_2,
       q.field_3,
       q.field_4,
       t.column5 as field_5
FROM table1 t
LEFT JOIN
  (
      -- calculate field_3 and field_4 for each col3
      select
        col3,
        SUM(col1) field_3,
        SUM(col2) field_4
      from  table1
      group by col3
  ) u
ON u.col3 = t.column1

Upvotes: 2

Sentinel
Sentinel

Reputation: 6449

No you can't return more than one column in a scalar sub query.

Instead you will need to leave it as is, or convert it to a join query. Since your sub queries aren't correlated with the outer query, you can use this:

select t.column1 as 'field_1',
       t.column2 as 'field_2',
       u.column3 as 'field_3',
       u.column4 as 'field_4',
       t.column5 as 'field_5'
  from table1 t
 cross join (select SUM(u.col1) as column3 
                  , SUM(u.col2) as column4
               from table1 u
              where u.col3 > 3) u;

In this way fields 3 and 4 will be computed once per execution instead of once (or twice) per record, and joined to every other record in table1.

Upvotes: 1

Squirrel
Squirrel

Reputation: 24763

you can use the window function SUM() OVER () with CASE WHEN .. to perform the conditional sum

select  t.column1 as 'field_1',
        t.column2 as 'field_2',
        sum(case when t.col3 > 3 then col1 end) over() as 'field3'  ,
        sum(case when t.col3 > 3 then col2 end) over() as 'field4'  ,
        t.column5 as 'field_5'
from    table1 t

also you don't required the derived table summ at all

Upvotes: 3

Related Questions