CODEWITHSUNDEEP
sqldatabasepostgresqlrelational-database
WriterState
WriterState

Reputation: 369

Explanation of PostgreSQL Query

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.

  1. 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?
  2. what is "as agg" doing in this query? It isn't referenced anywhere.

Upvotes: 0

Views: 93

Answers (3)

Yogus
Yogus

Reputation: 2272

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

  1. 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

  2. what is "as agg" doing in this query? It isn't referenced anywhere.

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

Upvotes: 0

gavintfn
gavintfn

Reputation: 529

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"

Upvotes: 0

RonCG
RonCG

Reputation: 369

  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.

  2. 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

Upvotes: 1

Related Questions