Geek Answers Handbook

Access column aliases in postgresql

Having a bit of trouble understanding how the request 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 column "count1" with the as command, but it seems like I cannot refer to this again in the query? I am trying to include the HAVING command at the end of this query to request only objects whose number is less than half the maximum value.

This is homework, but I am not asking for an answer just a pointer to how I can include the column count1 in another sentence.

Can anyone help?

+2
source share
1 answer

In the general case, you cannot reference the alias of an aggregate column later in the query, and you need to repeat the aggregate

If you really want to use his name, you can 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
+6
source

More articles:


All Articles