Code examples for SQL 1x1 blog-article - 16

sql1x1-16.sql

SELECT
  Genre,
  count(InvoiceId) AS Anzahl,
  avg(AnzahlProKauf) AS MWAnzahlProKauf
FROM (
  SELECT
    InvoiceId,
    c.Name AS Genre,
    count(a.TrackId) AS AnzahlProKauf
  FROM InvoiceLine a
  INNER JOIN Track b ON a.TrackId=b.TrackId
  LEFT JOIN Genre c ON b.GenreId=c.GenreId
  GROUP BY InvoiceId, b.GenreId
)
GROUP BY Genre
ORDER BY avg(AnzahlProKauf) DESC;