Using a select statement as one of the columns

Question

I have a query:

select 
      index_imsid
    , SUM(weighted_value) sumWeightedValue
    , (
        select 
              top 1 percentof
            , [Plan_Name_OR_Payment_Type] 
        from [v_PlanPerProvider1] 
        where [PLAN_RANK]=1
      ) plan1
from [v_PlanPerProvider1]
where plan_rank between 1 and 10
group by index_imsid
order by 1

and I am getting this error:

Msg 116, Level 16, State 1, Line 3
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.

Can you please help me to understand why I am getting this error?

It appears that it does not like the select statement as one of the columns?

Why do so many people dislike the return / enter key? Are horizontal scrollbars that cool-looking? — Aaron Bertrand
– Aaron Bertrand, Commented Sep 11, 2012 at 21:51
Because you have to hit 8 spaces after the return and the code will look more zigzagged? — Todd Li
– Todd Li, Commented Sep 11, 2012 at 23:12
I've edited the code and didn't hit the space 8 times... You can always copy the code, edit in an editor and paste it back. Faster, cleaner and no horizontal scrolling. What's more, in this case it is clear for the first glance already that too many values are selected in the subquery. — András Váczi
– András Váczi, Commented Sep 12, 2012 at 3:44

Todd Li · Accepted Answer · 2012-09-11 21:00:55Z

9

It does not like you selecting two columns in the subquery. You can only select one column at a time.

  select index_imsid, SUM(weighted_value) sumWeightedValue,
    (select top 1 percentof from [v_PlanPerProvider1] where [PLAN_RANK]=1) percentof
    (select top 1 [Plan_Name_OR_Payment_Type] from [v_PlanPerProvider1] where [PLAN_RANK]=1) Plan_Name_OR_Payment_Type
  from [v_PlanPerProvider1]
  where plan_rank between 1 and 10
  group by index_imsid
  order by 1

answered Sep 11, 2012 at 21:00

Todd Li

3,27923 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

GilM · Accepted Answer · 2012-09-11 21:39:52Z

3

Or you can do this:

select  
    t1.index_imsid, SUM(t1.weighted_value) sumWeightedValue, t2.percentof, t2.[Plan_Name_OR_Payment_Type]
from [v_PlanPerProvider1] t1
cross join (select top 1 percentof, [Plan_Name_OR_Payment_Type] from [v_PlanPerProvider1] where [PLAN_RANK]=1) t2 
where t1.plan_rank between 1 and 10 
group by t1.index_imsid, t2.percentof, t2.[Plan_Name_OR_Payment_Type]
order by 1

answered Sep 11, 2012 at 21:39

GilM

3,78120 silver badges18 bronze badges

4 Comments

Alex Gordon Over a year ago

cool!! can you explain please to me how the cross join works?

GilM Over a year ago

It means that every row of the joined table (in this case there will be only one) is joined to the restult set. So, all of the columns are available. If you guarantee only one row, it will not explode your result set, but if there were multiple rows, then your resultset would have its count multiplied by that number.

Alex Gordon Over a year ago

thanks so much! what is the diff between that and full outer join?

GilM Over a year ago

A Full outer join has join criteria (an ON clause). But, it will return all rows from both tables (left and right sides) even if the join condition is false (there will be NULLs for columns from the "other" table). Left Joins and Right Joins only return all of the non-matching rows from one side. A CROSS JOIN has no joining condition, it just adds all columns of all rows of the joined table to every row of the result set.

Collectives™ on Stack Overflow

Using a select statement as one of the columns

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related