Aggregate Function Error With Basic Query

My database is based on a cinema, I am trying to make a query which displays the number of tickets purchased for each movie screening. The tables involved in this query are Bookings, Screenings and Movies.

Here is the Bookings table:

The Screenings table:

And the Movies table:

I have so far managed to get the query to the point where I am able to display the amount of bookings for each screening, but this doesn’t take into account the fact that there could be multiple tickets purchased on a single booking, hence the Quantity column in Bookings:

Here is the SQL code that got me to this point:

SELECT Count(Bookings.[Booking ID]) 
AS [Bookings Per Screening], 
Bookings.[Screening ID], 
Movies.Movie 
FROM Movies 
INNER JOIN (Screenings 
INNER JOIN Bookings 
ON 
Screenings.[Screening ID] = Bookings.[Screening ID]) 
ON Movies.[Movie ID] = Screenings.[Movie ID] 
GROUP BY 
Bookings.[Screening ID], Movies.Movie, 
Screenings.[Movie ID], Movies.[Movie ID];

But when I try to add in a field which calculates the amount of tickets purchased per screening, rather than the amount of bookings made per screening, there are multiple rows displayed for each screening:

Here is the code that produced that:

SELECT Count(Bookings.[Booking ID]) 
AS [Bookings Per Screening], 
Bookings.[Screening ID], 
Movies.Movie, 
[Bookings Per Screening]*[Quantity] AS [Ticket Per Booking], 
Bookings.Quantity
FROM Movies 
INNER JOIN (Screenings 
INNER JOIN Bookings 
ON Screenings.[Screening ID] = Bookings.[Screening ID]) 
ON Movies.[Movie ID] = Screenings.[Movie ID]
GROUP BY Bookings.[Screening ID], Movies.Movie, 
Screenings.[Movie ID], Movies.[Movie ID], Bookings.Quantity;

What must I do to get this to work?

Answer

Stop trying to count Bookings and multiply them.

Each row already tells you how many tickets were sold.

SELECT
  [Screenings].[Screening Date],
  [Screenings].[Screening Time],
  [Movie].[Movie],
  SUM([Bookings].[Quantity]) AS [Seats Bought]
FROM
  [Bookings]
INNER JOIN
  [Screenings]
ON [Bookings].[Screening ID] = [Screenings].[Screening ID]
INNER JOIN
   [Movies]
ON  [Movies].[Movie ID] = [Screenings].[Movie ID]
GROUP BY
  [Screenings].[Screening Date],
  [Screenings].[Screening Time],
  [Movie].[Movie];

Advertisement

Answer