Select from nested select T-SQL

Question

I wish to modify data by selecting them in a inner query and count one of them modified.. It gives error..

select count(cvs) from
(
  select 
  cvs,
  (case Citycode when 123 then 'test' else 'other' end) as CityName ,
  (case ProductCode when '000' then 'test3' when 'ss' then 'xtr' else 'ddd' end) as CardName
  from Applications
)

If all you care about is the count, why are you selecting all of that other data? You're not grouping or aggregating any of the sub query data, so why not just select the count without the sub query? — George Johnston
– George Johnston, Commented Aug 8, 2011 at 20:39
Probably all you are missing is an alias. Select Count(*) from (...) A — YetAnotherUser
– YetAnotherUser, Commented Aug 8, 2011 at 20:39
my case is quite different just I simplify the question guys.. But it solved by using giving the alias of the query.. thx for help — zapoo
– zapoo, Commented Aug 8, 2011 at 20:48

Dalen · Accepted Answer · 2011-08-08 20:39:07Z

80

you need to give an alias to the subquery:

select count(x.cvs) from
(
  select 
  cvs,
  (case Citycode when 123 then 'test' else 'other' end) as CityName ,
  (case ProductCode when '000' then 'test3' when 'ss' then 'xtr' else 'ddd' end) as CardName
  from Applications
) x

answered Aug 8, 2011 at 20:39

Dalen

9,0264 gold badges49 silver badges53 bronze badges

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

Comments

Joe Stefanelli · Accepted Answer · 2011-08-08 20:39:21Z

1

Why not just do this instead?

SELECT COUNT(cvs)
    FROM Applications

answered Aug 8, 2011 at 20:39

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

1 Comment

JNK Over a year ago

I'm guessing (hoping) he has more logic he didn't include

Quintin Robinson · Accepted Answer · 2011-08-08 20:39:12Z

0

It appears your query could be simplified to..

SELECT COUNT(cvs) FROM Applications

Is there a reason you have the select nested and you are ignoring the other columns being selected?

answered Aug 8, 2011 at 20:39

Quintin Robinson

82.6k14 gold badges128 silver badges132 bronze badges

1 Comment

zapoo Over a year ago

thx Quintin but my case was related by using alias for sub query

JNK · Accepted Answer · 2011-08-08 20:40:18Z

0

Two things I see off the bat:

1 - You don't need the nested subquery for what you are doing in the example. You could just as easily do:

SELECT COUNT(cvs) FROM application

2 - You need an alias for the subquery, like (<subquery>) as SubQ

answered Aug 8, 2011 at 20:40

JNK

65.5k15 gold badges127 silver badges138 bronze badges

Collectives™ on Stack Overflow

Select from nested select T-SQL

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related