I couldn’t find a way to solve this task with the EXCEPT-Operator so I came up with another solution. Instead of the EXCEPT I counted the DISTINCT track_id’s from the album_ids.
WITH cat_purchase AS ( SELECT il.invoice_id, CASE WHEN COUNT(DISTINCT t.album_id) = 1 AND COUNT(DISTINCT t.track_id) = c.count_album_tracks THEN 'album' ELSE 'single' END AS purchase_type, c.count_album_tracks FROM track t JOIN invoice_line il ON il.track_id = t.track_id JOIN (SELECT COUNT(*) AS count_album_tracks, album_id FROM track GROUP BY album_id) c ON c.album_id = t.album_id GROUP BY invoice_id ) SELECT purchase_type, COUNT(*) AS number_of_invoices, ROUND(CAST(COUNT(*) AS float) / CAST((SELECT COUNT(*) FROM invoice) as float), 2) AS '%_of_invoices' FROM cat_purchase GROUP BY purchase_type
Yeah, it’s quite messy but it worked and for me it was way easier than using EXCEPT.
What’s your opinion about the code above?
I’m open to improvement suggestions.
Thanks in advance,