Styjun

I have to write a query that would calculate number of tickets purchased consisting only of movie genre of that type. At the end, I have to return movie genre and number of tickets bought for that genre. I have written a query but I was wondering if it can be made shorter and more compact?

Following is the database scheme:

movies(movieId, movieGenre, moviePrice)
tickets(ticketId, ticketDate, customerId)
details(ticketId, movieId, numOfTickets)

Here is my query:

select m.genre, count(*)
from(select t.ticketId, m.genre
 from(select d.ticketId
 from(select m.genre, t.ticketId
 from tickets t join details d on t.ticketId = 
 d.ticketId join movies m on d.movieId = m.movieId
 group by m.genre, t.ticketId) d
 group by d.ticketId
 having count(*) = 1) as t join details d on t.ticketId = 
 d.ticketId join movies m on d.movieId = m.movieId
 group by t.ticketId, m.genre) m
group by m.genre;

This runs on a database so I am only able to post sample output:

comedy 29821
action 27857
rom-com 19663

I see no reason to use the table tickets, because the results do not filter or aggregate by ticketDate or customerID. Thus, a shorter sql is

SELECT m.moviegenre,
 Sum(d.numoftickets) as SumNum
FROM details d
LEFT JOIN movies m
 ON d.movieid = m.movieid
GROUP BY m.moviegenre 
HAVING SumNum > 0
ORDER BY m.moviegenre

added 3/28 am

I am not sure what is meant by Duplicates?? In table = details(ticketId, movieId, numOfTickets) ??

I would expect that ticketId is unique, so what would explain duplicates?

Is the same ticketId being printed twice, repeatedly??

Determine what number of ticketId are duplicates--

SELECT ticketId, count(*) as cnt
FROM details d
GROUP By ticketId
HAVING count(*) > 1

Determine what number of "details" rows are duplicates--

SELECT ticketId, movieId, numOfTickets, count(*) as cnt
FROM details d
GROUP By ticketId, movieId, numOfTickets
HAVING count(*) > 1

Then again, it may be that table = movies(movieId, movieGenre, moviePrice) is the one with duplicates??
Determine what number of movieId are duplicates--

SELECT movieId, count(*) as cnt
FROM movies m
GROUP BY movieId
HAVING count(*) > 1

Remove duplicates from details--

SELECT m.moviegenre,
 Sum(d.numoftickets) as SumNum
FROM
 (Select Distinct * From details) d
LEFT JOIN movies m
 ON d.movieid = m.movieid
GROUP BY m.moviegenre 
ORDER BY m.moviegenre