SQL Server - invalid column name error in select query

Asked By muthuraman alexander on 07-Sep-11 02:27 AM
HI ALL,

i have a query like this

select ID, mark1,mark2,mark3,
     (mark1+mark2+mark3) AS TOTAL,
      ( TOTAL/3) As MEAN
from student


when i use this query i showing the error like this
invalid column name "TOTAL"


i need help for this

thanks in advance
MUTHU
smr replied to muthuraman alexander on 07-Sep-11 02:31 AM
HI

Check whether the column "TABLE" exists in your table or not.
muthuraman alexander replied to smr on 07-Sep-11 02:32 AM
it was not there in table ,,but i have given a alice name to the columns which are in the table


thanks for ur reply
MUTHU
Web Star replied to muthuraman alexander on 07-Sep-11 02:33 AM
Yest Total is not column name in your table if you want mean then change your query as follows

Select ID, mark1,mark2,mark3,
(mark1 + mark2 + mark3) As Total,
(mark1,mark2,mark3)/3 As MEAN
From Student


Riley K replied to muthuraman alexander on 07-Sep-11 02:38 AM
You cannot use Alias name as column Name

use like this
select ID, mark1,mark2,mark3,
   (mark1+mark2+mark3) AS TOTAL,
    ( mark1+mark2+mark3/3) As MEAN
from student

Jitendra Faye replied to muthuraman alexander on 07-Sep-11 02:39 AM
You are getting this error because column total is not present in table.

if you want to calculate the mean then change your command like this-

select ID, mark1,mark2,mark3,
   (mark1+mark2+mark3) AS TOTAL,
    ((mark1+mark2+mark3)/3) As MEAN

from student
  

Try this and let me know.
Reena Jain replied to muthuraman alexander on 07-Sep-11 02:40 AM
Hi,

Actually the total you are taking of 3 columns and giving the aliases to column in select query, it is only a temporary column name, so you need to use the query like this

select ID, mark1,mark2,mark3,
   (mark1+mark2+mark3) AS TOTAL,
    ((mark1+mark2+mark3)/3) AS Average
from student

try this and let me know
aneesa replied to muthuraman alexander on 07-Sep-11 03:12 AM
change your query to

select ID, mark1,mark2,mark3,
   (mark1+mark2+mark3) AS TOTAL, (mark1+mark2+mark3)/3 as MEAN

because 'TOTAL' is just an alias name, it actually does not exist. So you have to sum up the columns mark1+mark2+mark3 and then find the average

aneesa replied to muthuraman alexander on 07-Sep-11 03:13 AM
select ID, mark1,mark2,mark3,
   (mark1+mark2+mark3) AS TOTAL, (mark1+mark2+mark3)/3 as MEAN from student

because 'TOTAL' is just an alias name, it actually does not exist. So you have to sum up the columns mark1+mark2+mark3 and then find the average