sql using aliases

Question

I seem to be having some trouble grasping the concept of using AS to create an alias, and then using that alias later. Here is an example of a very simple query that I get an error when I run:

SELECT IP,  
       (SELECT server_ip as IP  
          FROM table1  
         WHERE start_time BETWEEN @startdate AND @enddate  
      Group By server_ip)X  
Group By IP 

I would expect this to just return the results from the second select statement in the server_ip column. However I get an error messages saying IP is not an invalid column name. Can someone explain to me how to do this properly?

Answer 1

SELECT IP,
    (
    SELECT server_ip as IP
    FROM table1
    WHERE start_time BETWEEN @startdate AND @enddate
    Group By server_ip
    )X
/*FROM WHERE ? <--------------- */
Group By IP 

Your SELECT statement at the moment is

SELECT IP, X
GROUP BY IP

You need a FROM clause. X is a non correlated sub query at the moment. I suspect that you wanted to treat it as a derived table but even then the query makes no sense. There is no need to perform the same GROUP BY action twice.

Dependant upon your RDBMS you might also be able to use Common Table Expressions. Here's a somewhat contrived example of their use.

;WITH X AS
(
    SELECT server_ip as IP
    FROM table1
    WHERE start_time BETWEEN @startdate AND @enddate
),
Y AS
(
    SELECT IP
    FROM X
    Group By IP
)
SELECT IP
FROM Y

Answer 2

You were missing the FROM clause:

  SELECT x.ip
    FROM (SELECT server_ip as IP  
            FROM table1  
           WHERE start_time BETWEEN @startdate AND @enddate  
        GROUP BY server_ip) AS x 
GROUP BY x.ip 

As is, you only need to use:

  SELECT server_ip as IP  
    FROM table1  
   WHERE start_time BETWEEN @startdate AND @enddate  
GROUP BY server_ip

Answer 3

you have to use "AS" keyword befor your new name for assigning a alias to a temp table or column or etc.

Answer 4

select t.field,t.field2
from anytablename t
where...

that is setting up t as an alias for anytablename. Queries can also be used in this manner

select t.field,t.field2
from (select field1,field2 from anymytable where...) t
where...

I think OMG Ponies has what you want to do for this query, no need to repeat him. Remember the select statement you are selecting from (the subquery) goes in the from clause (not the select) and behaves (mostly) like a table.