For our first foray into aggregates, we’re going to stick to something simple. We want to know how many facilities exist - simply produce a total count.

    2. count = query.scalar()
    4. # OR:
    5. count = Facility.select().count()

    Count the number of expensive facilities

    Produce a count of the number of facilities that have a cost to guests of 10 or more.

    1. SELECT COUNT(facid) FROM facilities WHERE guestcost >= 10
    1. query = Facility.select(fn.COUNT(Facility.facid)).where(Facility.guestcost >= 10)
    2. count = query.scalar()
    4. # OR:
    5. # count = Facility.select().where(Facility.guestcost >= 10).count()

    Count the number of recommendations each member makes.

    Produce a count of the number of recommendations each member has made. Order by member ID.

    1. SELECT recommendedby, COUNT(memid) FROM members
    2. WHERE recommendedby IS NOT NULL
    3. GROUP BY recommendedby
    4. ORDER BY recommendedby
    1. query = (Member
    2.          .select(Member.recommendedby, fn.COUNT(Member.memid))
    3.          .where(Member.recommendedby.is_null(False))
    4.          .group_by(Member.recommendedby)
    5.          .order_by(Member.recommendedby))

    List the total slots booked per facility

    Produce a list of the total number of slots booked per facility. For now, just produce an output table consisting of facility id and slots, sorted by facility id.

    1. SELECT facid, SUM(slots) FROM bookings GROUP BY facid ORDER BY facid;
    1. query = (Booking
    2.          .select(Booking.facid, fn.SUM(Booking.slots))
    3.          .group_by(Booking.facid)
    4.          .order_by(Booking.facid))

    List the total slots booked per facility in a given month

    Produce a list of the total number of slots booked per facility in the month of September 2012. Produce an output table consisting of facility id and slots, sorted by the number of slots.

    1. SELECT facid, SUM(slots)
    2. FROM bookings
    3. WHERE (date_trunc('month', starttime) = '2012-09-01'::dates)
    4. GROUP BY facid
    5. ORDER BY SUM(slots)
    1. query = (Booking
    2.          .select(Booking.facility, fn.SUM(Booking.slots))
    3.          .where(fn.date_trunc('month', Booking.starttime) == datetime.date(2012, 9, 1))
    4.          .group_by(Booking.facility)
    5.          .order_by(fn.SUM(Booking.slots)))

    List the total slots booked per facility per month

    Produce a list of the total number of slots booked per facility per month in the year of 2012. Produce an output table consisting of facility id and slots, sorted by the id and month.

    1. SELECT facid, date_part('month', starttime), SUM(slots)
    2. FROM bookings
    3. WHERE date_part('year', starttime) = 2012
    4. GROUP BY facid, date_part('month', starttime)
    5. ORDER BY facid, date_part('month', starttime)
    1. month = fn.date_part('month', Booking.starttime)
    2. query = (Booking
    3.          .select(Booking.facility, month, fn.SUM(Booking.slots))
    4.          .where(fn.date_part('year', Booking.starttime) == 2012)
    5.          .group_by(Booking.facility, month)
    6.          .order_by(Booking.facility, month))

    Find the total number of members who have made at least one booking.

    1. SELECT COUNT(DISTINCT memid) FROM bookings
    3. -- OR --
    4. SELECT COUNT(1) FROM (SELECT DISTINCT memid FROM bookings) AS _

    List facilities with more than 1000 slots booked

    Produce a list of facilities with more than 1000 slots booked. Produce an output table consisting of facility id and hours, sorted by facility id.

    1. SELECT facid, SUM(slots) FROM bookings
    2. GROUP BY facid
    3. HAVING SUM(slots) > 1000
    4. ORDER BY facid;
    1. query = (Booking
    2.          .select(Booking.facility, fn.SUM(Booking.slots))
    3.          .group_by(Booking.facility)
    4.          .having(fn.SUM(Booking.slots) > 1000)

    Find the total revenue of each facility

    1. SELECT f.name, SUM(b.slots * (
    2. CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
    3. FROM bookings AS b
    4. INNER JOIN facilities AS f ON b.facid = f.facid
    5. GROUP BY f.name
    6. ORDER BY revenue;
    1. revenue = fn.SUM(Booking.slots * Case(None, (
    2.     (Booking.member == 0, Facility.guestcost),
    3. ), Facility.membercost))
    5. query = (Facility
    6.          .select(Facility.name, revenue.alias('revenue'))
    7.          .join(Booking)
    9.          .order_by(SQL('revenue')))

    Find facilities with a total revenue less than 1000

    Produce a list of facilities with a total revenue less than 1000. Produce an output table consisting of facility name and revenue, sorted by revenue. Remember that there’s a different cost for guests and members!

    1. SELECT f.name, SUM(b.slots * (
    2. CASE WHEN b.memid = 0 THEN f.guestcost ELSE f.membercost END)) AS revenue
    3. FROM bookings AS b
    4. INNER JOIN facilities AS f ON b.facid = f.facid
    5. GROUP BY f.name
    6. HAVING SUM(b.slots * ...) < 1000
    7. ORDER BY revenue;
    1. # Same definition as previous example.
    2. revenue = fn.SUM(Booking.slots * Case(None, (
    3.     (Booking.member == 0, Facility.guestcost),
    4. ), Facility.membercost))
    6. query = (Facility
    7.          .select(Facility.name, revenue.alias('revenue'))
    8.          .join(Booking)
    9.          .group_by(Facility.name)
    10.          .having(revenue < 1000)
    11.          .order_by(SQL('revenue')))

    Output the facility id that has the highest number of slots booked

    Output the facility id that has the highest number of slots booked.

    1. SELECT facid, SUM(slots) FROM bookings
    2. GROUP BY facid
    3. ORDER BY SUM(slots) DESC
    4. LIMIT 1
    1. query = (Booking
    2.          .select(Booking.facility, fn.SUM(Booking.slots))
    3.          .group_by(Booking.facility)
    4.          .order_by(fn.SUM(Booking.slots).desc())
    5.          .limit(1))
    7. # Retrieve multiple scalar values by calling scalar() with as_tuple=True.
    8. facid, nslots = query.scalar(as_tuple=True)

    List the total slots booked per facility per month, part 2

    Produce a list of the total number of slots booked per facility per month in the year of 2012. In this version, include output rows containing totals for all months per facility, and a total for all months for all facilities. The output table should consist of facility id, month and slots, sorted by the id and month. When calculating the aggregated values for all months and all facids, return null values in the month and facid columns.

    Postgres ONLY.

    1. SELECT facid, date_part('month', starttime), SUM(slots)
    2. FROM booking
    3. WHERE date_part('year', starttime) = 2012
    4. GROUP BY ROLLUP(facid, date_part('month', starttime))
    5. ORDER BY facid, date_part('month', starttime)
    1. month = fn.date_part('month', Booking.starttime)
    2. query = (Booking
    3.          .select(Booking.facility,
    4.                  month.alias('month'),
    5.                  fn.SUM(Booking.slots))
    6.          .where(fn.date_part('year', Booking.starttime) == 2012)
    7.          .group_by(fn.ROLLUP(Booking.facility, month))
    8.          .order_by(Booking.facility, month))

    Produce a list of the total number of hours booked per facility, remembering that a slot lasts half an hour. The output table should consist of the facility id, name, and hours booked, sorted by facility id.

    1. SELECT f.facid, f.name, SUM(b.slots) * .5
    2. FROM facilities AS f
    3. INNER JOIN bookings AS b ON (f.facid = b.facid)
    4. GROUP BY f.facid, f.name
    5. ORDER BY f.facid
    1. query = (Facility
    2.          .select(Facility.facid, Facility.name, fn.SUM(Booking.slots) * .5)
    3.          .join(Booking)
    4.          .group_by(Facility.facid, Facility.name)
    5.          .order_by(Facility.facid))

    List each member’s first booking after September 1st 2012

    Produce a list of each member name, id, and their first booking after September 1st 2012. Order by member ID.

    1. query = (Member
    2.          .select(Member.surname, Member.firstname, Member.memid,
    3.                  fn.MIN(Booking.starttime).alias('starttime'))
    4.          .join(Booking)
    5.          .where(Booking.starttime >= datetime.date(2012, 9, 1))
    6.          .group_by(Member.surname, Member.firstname, Member.memid)
    7.          .order_by(Member.memid))

    Produce a list of member names, with each row containing the total member count

    Produce a list of member names, with each row containing the total member count. Order by join date.

    Postgres ONLY (as written).

    1. SELECT COUNT(*) OVER(), firstname, surname
    2. FROM members ORDER BY joindate
    1. query = (Member
    2.                  Member.surname)
    3.          .order_by(Member.joindate))

    Produce a numbered list of members

    Postgres ONLY (as written).

    1. SELECT row_number() OVER (ORDER BY joindate), firstname, surname
    2. FROM members ORDER BY joindate;
    1. query = (Member
    2.          .select(fn.row_number().over(order_by=[Member.joindate]),
    4.          .order_by(Member.joindate))

    Output the facility id that has the highest number of slots booked, again

    Output the facility id that has the highest number of slots booked. Ensure that in the event of a tie, all tieing results get output.

    Postgres ONLY (as written).

    1. SELECT facid, total FROM (
    2.   SELECT facid, SUM(slots) AS total,
    3.          rank() OVER (order by SUM(slots) DESC) AS rank
    4.   FROM bookings
    5.   GROUP BY facid
    6. ) AS ranked WHERE rank = 1
    1. rank = fn.rank().over(order_by=[fn.SUM(Booking.slots).desc()])
    3. subq = (Booking
    4.         .select(Booking.facility, fn.SUM(Booking.slots).alias('total'),
    5.                 rank.alias('rank'))
    6.         .group_by(Booking.facility))
    8. # Here we use a plain Select() to create our query.
    9. query = (Select(columns=[subq.c.facid, subq.c.total])
    10.          .from_(subq)
    11.          .where(subq.c.rank == 1)
    12.          .bind(db))  # We must bind() it to the database.
    14. # To iterate over the query results:
    15. for facid, total in query.tuples():
    16.     print(facid, total)

    Rank members by (rounded) hours used

    Produce a list of members, along with the number of hours they’ve booked in facilities, rounded to the nearest ten hours. Rank them by this rounded figure, producing output of first name, surname, rounded hours, rank. Sort by rank, surname, and first name.

    Postgres ONLY (as written).

    1. SELECT firstname, surname,
    2. ((SUM(bks.slots)+10)/20)*10 as hours,
    3. rank() over (order by ((sum(bks.slots)+10)/20)*10 desc) as rank
    4. FROM members AS mems
    5. INNER JOIN bookings AS bks ON mems.memid = bks.memid
    6. GROUP BY mems.memid
    7. ORDER BY rank, surname, firstname;
    1. hours = ((fn.SUM(Booking.slots) + 10) / 20) * 10
    2. query = (Member
    3.          .select(Member.firstname, Member.surname, hours.alias('hours'),
    4.                  fn.rank().over(order_by=[hours.desc()]).alias('rank'))
    5.          .join(Booking)
    6.          .group_by(Member.memid)
    7.          .order_by(SQL('rank'), Member.surname, Member.firstname))

    Produce a list of the top three revenue generating facilities (including ties). Output facility name and rank, sorted by rank and facility name.

    Postgres ONLY (as written).

    1. SELECT name, rank FROM (
    2.     SELECT f.name, RANK() OVER (ORDER BY SUM(
    3.         CASE WHEN memid = 0 THEN slots * f.guestcost
    4.         ELSE slots * f.membercost END) DESC) AS rank
    5.     FROM bookings
    6.     INNER JOIN facilities AS f ON bookings.facid = f.facid
    7.     GROUP BY f.name) AS subq
    8. WHERE rank <= 3
    9. ORDER BY rank;
    1. total_cost = fn.SUM(Case(None, (
    2.     (Booking.member == 0, Booking.slots * Facility.guestcost),
    3. ), (Booking.slots * Facility.membercost)))
    5. subq = (Facility
    6.         .select(Facility.name,
    7.                 fn.RANK().over(order_by=[total_cost.desc()]).alias('rank'))
    8.         .join(Booking)
    9.         .group_by(Facility.name))
    11. query = (Select(columns=[subq.c.name, subq.c.rank])
    12.          .from_(subq)
    13.          .where(subq.c.rank <= 3)
    14.          .order_by(subq.c.rank)
    15.          .bind(db))  # Here again we used plain Select, and call bind().

    Classify facilities by value

    Classify facilities into equally sized groups of high, average, and low based on their revenue. Order by classification and facility name.

    1. SELECT name,
    2.   CASE class WHEN 1 THEN 'high' WHEN 2 THEN 'average' ELSE 'low' END
    3. FROM (
    4.   SELECT f.name, ntile(3) OVER (ORDER BY SUM(
    5.     CASE WHEN memid = 0 THEN slots * f.guestcost ELSE slots * f.membercost
    6.     END) DESC) AS class
    7.   FROM bookings INNER JOIN facilities AS f ON bookings.facid = f.facid
    8.   GROUP BY f.name
    9. ) AS subq
    10. ORDER BY class, name;