I've created a table that lists venues in which several events take place. The same event in the same venue can have a different price. The last column calculates the total revenue for one venue.
SELECT venuename,
eventname,
totalprice,
SUM(totalprice) OVER (PARTITION BY venuename)
FROM venue
INNER JOIN event e ON venue.venueid = e.venueid
INNER JOIN listing l ON e.eventid = l.eventid;
venue | event | totalprice | sum |
---|---|---|---|
Venue A | Event A | 5 | 30 |
Venue A | Event A | 10 | 30 |
Venue A | Event B | 5 | 30 |
Venue A | Event A | 5 | 30 |
Venue A | Event B | 5 | 30 |
Venue B | Event C | 3 | 15 |
Venue B | Event D | 6 | 15 |
Venue B | Event D | 6 | 15 |
Now i want to group up equal events and calculate the total revenue of one event in a venue.
The result should look something like this:
venue | event | totalprice | sum |
---|---|---|---|
Venue A | Event A | 20 | 30 |
Venue A | Event B | 10 | 30 |
Venue B | Event C | 3 | 15 |
Venue B | Event D | 12 | 15 |
My idea to solve this was something like this:
SELECT venuename,
eventname,
SUM(totalprice),
SUM(totalprice) OVER (PARTITION BY venuename)
FROM venue
INNER JOIN event e ON venue.venueid = e.venueid
INNER JOIN listing l ON e.eventid = l.eventid
GROUP BY eventname;
But this doesn't seem to be possible since vanuename has to be part of an aggregate function as well. But i can't really come up with an idea to solve this problem.
Hope anyone of you guys can help me with this.
greetings
You can combine an agrregate and a window function
SELECT venuename,
eventname,
SUM(totalprice) totalprice,
SUM(SUM(totalprice)) OVER (PARTITION BY venuename) sum
FROM venue
INNER JOIN event e ON venue.venueid = e.venueid
INNER JOIN listing l ON e.eventid = l.eventid
GROUP BY venuename, eventname;