x

Invalid column name

When I run my query with a subquery I get Invalid column name 'Fail' What am I doing wrong? Thanks in advance

 select q.Fail
 from
  (Select 
 SUM(Policysin)as Total,
 SUM(Failed) as Failed,
 Inceptmonth,
 CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0 * [Failed] / [Policysin])) + '%' as [Fail]
 From Quarters
 Group By 
 Inceptmonth,
 Fail) as q
more ▼

asked Apr 25, 2012 at 04:30 PM in Default

avatar image

n3w2sql
870 22 32 39

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

The reason you get the error is because you try to group by the rows in your subquery by the column named Fail but this column is "not yet available" to the group by clause because of it's order of execution. One way to make your query work would be to restate it like this:

 select 
   q.Fail 
   from 
   (
     Select 
       SUM(Policysin)as Total, 
       SUM(Failed) as Failed, 
       Inceptmonth, 
       CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0*[Failed]/[Policysin]))+'%' Fail 
       From Quarters 
       Group By 
         Inceptmonth, 
         -- Fail, cannot work, the column name is not available yet
         -- but this does
         CONVERT(VARCHAR,CONVERT(DECIMAL(5,1),100.0*[Failed]/[Policysin]))
   ) as q;

This makes it somewhat ugly, but the bottom line is that you cannot use an aliased column in your group by and have to instead use the whole expression from your select statement to make it work.

Oleg

more ▼

answered Apr 25, 2012 at 06:33 PM

avatar image

Oleg
17.1k 3 7 28

@Oleg Netchaev You are right. +1. But I would rather calculate the Fail percentage outside the sub-query i.e. something like

 select 
   convert(varchar, convert(decimal(5,1), 100.0 * 
         Failed/Policysin)) + '%' as Fail
   from 
   (
     select 
       sum(Policysin)as Total, 
       sum(Failed) as Failed,        
       Inceptmonth
       from Quarters 
       group by Inceptmonth
   ) as q;
Apr 26, 2012 at 05:01 AM Usman Butt

Thank you so much that worked perfectly.

Apr 26, 2012 at 09:04 AM n3w2sql

@Usman Butt Yep, it is much better to do the percentage calculation only once of course. I just wanted to preserve the original shape of the query to simply point out the source of the original problem.

Apr 26, 2012 at 02:41 PM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x2076

asked: Apr 25, 2012 at 04:30 PM

Seen: 1155 times

Last Updated: Apr 26, 2012 at 02:41 PM

Copyright 2016 Redgate Software. Privacy Policy