CODEWITHSUNDEEP
sqldatabase
MilqueToasted
MilqueToasted

Reputation: 83

sql using aliases

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?

Upvotes: 1

Views: 99

Answers (3)

Masoud DaneshPour
Masoud DaneshPour

Reputation: 126

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

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 452977

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

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332521

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

Upvotes: 1

Related Questions