Unique query is giving duplicates

[iankerry]

Hi All,

I don't know if you can help.

I have a unique query which lists all the films that we are screening over the next 3 months. I have added a COUNT field so that I can see how many of each films we are screening.

The problem is that i get duplicates of some films - and this may be because we may hold several copies of some films. I have attached two images which might explain this better!

What I could do with is knowing how to make it so that i get a list of films booked and how many of each, regardless of which copy of the film is used.

The SQL is:

SELECT DISTINCTROW dbo_Films.[film name], Count(dbo_Films.[film name]) AS [CountOffilm name]
FROM ((dbo_Films INNER JOIN dbo_filmCopies ON dbo_Films.ID = dbo_filmCopies.tblFilms_ID) INNER JOIN dbo_EventsFlicks ON dbo_filmCopies.ID = dbo_EventsFlicks.filmCopyID) INNER JOIN dbo_Venues ON dbo_EventsFlicks.venueID = dbo_Venues.ID
WHERE (((dbo_EventsFlicks.datefield)>=#8/1/2015# And (dbo_EventsFlicks.datefield)<#1/1/2016#))
GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised
ORDER BY dbo_Films.[film name];

I hope this is understandable.

Thanks

Ian

 

[plog]

Because of your GROUP BY clause:

GROUP BY dbo_Films.[film name], dbo_Venues.southhub, dbo_Venues.northhub, dbo_Films.Specilaised

Why do you have fields in the GROUP BY that aren't in the SELECT?

 

[jdraw]

Further to plog's question, do you have a clear statement --in plain English -- of WHAT you are trying to do?
I don't understand this

a list of films booked and how many of each, regardless of which copy of the film is used.

 

[iankerry]

Because of your GROUP BY clause:



Why do you have fields in the GROUP BY that aren't in the SELECT?

Oh God, stupid me, I never thought it was because of these - I sometimes have to use these groupings, but never thought they would interfere with what I was trying to do. See,s like I had already decided it was something else - serves me right.

When I took them out it worked perfectly. Thanks Plog

 

[iankerry]

Further to plog's question, do you have a clear statement --in plain English -- of WHAT you are trying to do?
I don't understand this

Sorry it wasn't clear. But all solved now, thanks for replying.

I just wanted a list of unique films, with the count of how many times they appear, but i was getting several film names twice, as you can see from the images.