Re: Understanding aggregation and window functions.
| От | David G. Johnston |
|---|---|
| Тема | Re: Understanding aggregation and window functions. |
| Дата | |
| Msg-id | CAKFQuwZE3RANDXgYkJqwdB5iWwDnaGsqQSdhRH2KQYTP3BtsCA@mail.gmail.com обсуждение исходный текст |
| Ответ на | Understanding aggregation and window functions. (Pól Ua Laoínecháin <linehanp@tcd.ie>) |
| Список | pgsql-novice |
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.
with subquery as (select id, val)
from mainquery
Though, as can be seen from the above, usually a join works better than a subquery in cases like this.
David J.
В списке pgsql-novice по дате отправления: