Explanation of PostgreSQL Query

Question

I'm having trouble unpacking this postgreSQL query:

   select name, revenue from (
    select facs.name, sum(case 
                when memid = 0 then slots * facs.guestcost
                else slots * membercost
            end) as revenue
        from cd.bookings bks
        inner join cd.facilities facs
            on bks.facid = facs.facid
        group by facs.name
    ) as agg where revenue < 1000
order by revenue;

Here are my questions.

the outer query is pulling "revenue" from the table returned by the inner query, which has a column name "revenue" as well. Is the outer query simply pulling the column from the inner query with this reference?
what is "as agg" doing in this query? It isn't referenced anywhere.

RonCG · Accepted Answer · 2020-09-08 01:53:20Z

1

Yes. The outer query just references the value of "revenue" of the inner query which, in this case, is the result of the sum() function.
It's an alias for the result. If, for any reason, you want to use the result of your outer query, in another query, you reference it as 'agg'. For example, lets say I want to get the revenue < 500 of the initial result, I would create the following query:

SELECT revenue FROM agg WHERE revenue < 500

answered Sep 8, 2020 at 1:53

RonCG

3691 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

RonCG Over a year ago

Glad I could help!

gavintfn Over a year ago

To use the results from the outer query, you would have to assign its generated result a new alias. SELECT revenue FROM ( select name, revenue from (....... ....... order by revenue; ) as rev WHERE revenue < 500

gavintfn · Accepted Answer · 2020-09-08 02:15:14Z

The inner query is querying the table 'bookings' (and naming it bks) it is joining the table 'facilities' (and naming it facs). The join is occurring on bookings.facid being equal to facilities.facid

The generated results of that join are being called "agg". There must be multiple entries with the same name in the 'facilities', as the results are grouped by facilities.'name'. 'revenue' is being calculated by the sum() function, which is calculating a total using all of the entries consisting of the same 'facilities'.'name'

"agg" is the name given to the results of inner query, as all generated results must be named, but does not need to be referenced in the outer query because 'name' and 'revenue' are unique in the outer query. So, "SELECT name, revenue" is the same as "SELECT agg.name, agg.revenue"

Yogus · Accepted Answer · 2020-09-08 02:39:09Z

Read it like this

There is inner query which fetches you two columns name and revenew from joins

Below part is your revenew from bookings table

sum(case when memid = 0 then slots * facs.guestcost else slots * membercost end) as revenue

The booking table is then joined with facilities table like this
inner join cd.facilities facs on bks.facid = facs.facid

Since this is inner query so alias is must to give like agg

after when the inner query returns the data , the main select query select name, revenue extracts the data <1000

the outer query is pulling "revenue" from the table returned by the inner query, which has a column name "revenue" as well. Is the outer query simply pulling the column from the inner query with this reference? YES
what is "as agg" doing in this query? It isn't referenced anywhere.

its just an alias to tag that from as a table.

Collectives™ on Stack Overflow

Explanation of PostgreSQL Query

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related