Re: Understanding aggregation and window functions.

SELECT
  th.theatre_name AS "Theatre name", s.show_name AS "Show name",
  SUM(t.price) AS "Total sales"

Theatre name    Show name   Sales/show
      Theatre_1         show_10             2050
      Theatre_1          show_11                77
      Theatre_2         show_20               200
      Theatre_2          show_21                63

Again, fine! But what I want is (theatre_1 2127) and (theatre_2, 263).

  SUM(t.price) OVER (PARTITION BY th.theatre_id ORDER BY th.theatre_id),

and I receive the error:

ERROR: column "t.price" must appear in the GROUP BY clause or be used
in an aggregate function LINE 8: SUM(t.price) OVER (PARTITION BY
th.theatre_id ORDER BY th....

** BONUS QUESTION **

I would also like to include the number of tickets sold by price point
and the number of tickets sold per theatre and per show - this might
involve a subselect in the SELECT clause?

  SELECT COUNT(t.ticket_show_id)
  FROM show s
  JOIN ticket t ON s.show_id = t.ticket_show_id
  GROUP BY s.show_id

Result

count
9
7
3
10

But when I tried to integrate it into another query as a SELECT in the
SELECT statement, I received the error:

  ERROR: more than one row returned by a subquery used as an expression 

Which I can understand, but can't figure out how to solve. For each
row of my main query, the query above should only return 1 row -
but...