is possible to apply an aggregate function on a alias in a list of select statement?

F. Y. 0
Viewed 38

I wrote a query as below, and the result shows: ERROR: syntax error at or near "rank" Position: 58. I don't know why it is wrong and I guess that is because aggregate not allowed to work on alias"hours". Can someone help me clarify this question? I appreciate it.

SELECT
    firstname,
    surname,
    (sum(bks.slots * 0.5)) AS hours rank(hours) DESC AS rank
FROM
    cd.members mem
    INNER JOIN cd.bookings bks ON mem.memid = bks.memid
GROUP BY
    firstname,
    surname
ORDER BY
    hours DESC;
postgresql
1 Answers

I guess first you want to calculate the hours and then rank it. If so, then select statement should be something like that:

SELECT
    firstname,
    surname,
    hours,
    rank() OVER (ORDER BY hours DESC) AS ranking
FROM (
    SELECT
        firstname,
        surname,
        (sum(bks.slots * 0.5)) AS hours
    FROM
        cd.members mem
        INNER JOIN cd.bookings bks ON mem.memid = bks.memid
    GROUP BY
        firstname,
        surname) AS tab1
ORDER BY
    ranking DESC;
Saya0
Related