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

n3w2sql gravatar image

n3w2sql
850 14 21 33

(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.

<!-- Begin Edit

Actually, looking at the query in your question, the logic has a flaw, because the group by does not actually do what you need it to do. It looks like all you need is the sum of Policysin, sum of Failed and the ratio of Failed to Policysin expressed as percentage. But this means that you should not have anything in the group by except the Inceptmonth column and the aggregates should be applied in the actual calculation of that percentage. Including this calculation in the group by will not produce expected result. You query therefore has to be restated. Something like this will give you the data you need:

select 
  q.Fail 
  from 
  (
    select 
      sum(Policysin)as Total, 
      sum(Failed) as Failed,        
      Inceptmonth,
      convert(varchar, convert(decimal(5,1), 100.0 * 
        sum(Failed)/sum(Policysin))) + '%' as Fail
      from Quarters 
      group by Inceptmonth
  ) as q;

End Edit -->

Oleg
more ▼

answered Apr 25, 2012 at 06:33 PM

Oleg gravatar image

Oleg
15.9k 2 4 24

@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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x1832

asked: Apr 25, 2012 at 04:30 PM

Seen: 829 times

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