grouping equal values – aggregate function problem

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

Answer

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;

Advertisement

Answer