4

Having a little bit of trouble understanding how a query alias works in postgresql. I have the following:

SELECT DISTINCT robber.robberid,
                nickname,
                Count(accomplices.robberid) AS count1
FROM   robber
       INNER JOIN accomplices
               ON accomplices.robberid = robber.robberid
GROUP  BY robber.robberid,
          robber.nickname
ORDER  BY Count(accomplices.robberid) DESC;


 robberid |            nickname            | count1 
----------+--------------------------------+--------
       14 | Boo Boo Hoff                   |      7
       15 | King Solomon                   |      7
       16 | Bugsy Siegel                   |      7
       23 | Sonny Genovese                 |      6
        1 | Al Capone                      |      5
 ...

I can rename the "count1" column using the as command but I can't seem to be able to refer to this again in the query? I am trying to include a HAVING command at the end of this query to query only objects who have a count less than half of the max.

This is homework but I am not asking for the answer only a pointer to how I can include the count1 column in another clause.

Can anyone help?

Taryn
  • 242,637
  • 56
  • 362
  • 405
jmc
  • 620
  • 14
  • 24

1 Answers1

7

In general, you can't refer to an aggregate column's alias later in the query, and you have to repeat the aggregate

If you really want to use its name, you could wrap your query as a subquery

SELECT * 
FROM
(
    SELECT DISTINCT robber.robberid, nickname, count(accomplices.robberid)  
    AS count1 FROM robber                   
    INNER JOIN accomplices  
    ON accomplices.robberid = robber.robberid  
    GROUP BY robber.robberid, robber.nickname  
) v
ORDER BY count1 desc
podiluska
  • 50,950
  • 7
  • 98
  • 104
  • Thanks a lot - this helped clear up my confusion using subqueries. – jmc Aug 20 '12 at 10:31
  • 6
    @jmc In case you're wondering *why* you can't refer to the `SELECT` aliases later in the query, like in the `WHERE` clause, it's all to do with order of evaluation. SQL isn't evaluated in the order it's written. The `SELECT`-list is actually evaluated almost last, and its contents effectively don't exist yet when `HAVING` etc are processed. This becomes important when the select-list includes functions with side-effects; they should only get executed if the `WHERE` clause matched. – Craig Ringer Aug 20 '12 at 11:50
  • Thanks Craig. Wasn't very well explored in my DB course so this has been a lot of help. – jmc Aug 21 '12 at 09:25