Im still quite new to SQL and I cant figure out how to get the total revenue for each specific genre in my database.
The problem is that I cant just use SUM(prijs) from the Tabel 'Prijzen' which means price in english. I have to check for each genre how many seats were occupied(tickets sold) in the table 'Bezetting'. A seat has its associated rank which u find in the table 'Rang'(rangnummer) , this influences the price of that ticket.
CREATE OR ALTER VIEW vw_OmzetPerGenre AS
SELECT V.Genre, P.voorstellingsnummer, R.rangnummer, MIN(CONVERT(date, begindatumtijd)) AS Datum, SUM(P.Prijs) AS Omzet
FROM Bezetting B
INNER JOIN
Uitvoering U ON B.voorstellingsnummer = U.voorstellingsnummer
INNER JOIN
Voorstelling V ON U.voorstellingsnummer = V.voorstellingsnummer
INNER JOIN
Prijzen P ON P.voorstellingsnummer = V.voorstellingsnummer
INNER JOIN Rang R ON R.Rangnummer = P.Rangnummer
GROUP BY V.Genre, P.Voorstellingsnummer, R.Rangnummer
This is the code I got but doesn't look at how many seats were occupied and actually made money. Im writing this code to put it in PowerBI for a project but I can't seem to figure this one out myself.
The result I want is that for each genre I get a total revenue. That has looked at how many seats were sold and what rank those seats were. For Example: Then you get how many seats of rank 1 and 2 were sold for the 'Muziek'. The sum of sold seats rank 1 x the according price + the sum of sold seats rank 2 x the according price
This link goes to the CREATE and INSERT for the database im using