On Wed, Jan 6, 2021 at 9:08 AM Pól Ua Laoínecháin <linehanp@tcd.ie> wrote:
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).
Then why are you grouping on "Show name" if the total you want is "Theatre name"?
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....
sum(sum(t.price)) over (...)
When using window function in a aggregate query you need to write one aggregate function calls to appease the group by (the inner sum) and one aggregate function call to appease the "over" (the outer sum).
Ignoring the rest of the section, re-ask if you still have questions after getting clarity for the above.
** 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
I try to never write a group by query that doesn't include a column for each grouped field, then any aggregates.
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...
Then you probably need to correlate the outer and inner queries by referencing a value in the outer query inside of the subquery.