RedHood148 RedHood148 - 6 months ago 9
SQL Question

Finding the highest value of an attribute using ALL in SQLite

I am supposed to write a query to find "Find name(s) of the artist(s) with the highest total sales".
The query that I wrote is this one:

SELECT Ar.Name, SUM(IL.UnitPrice) AS ts
FROM Artist Ar
JOIN Album Al ON Ar.ArtistId=Al.ArtistId
JOIN Track Tr ON Al.AlbumId=Tr.AlbumId
JOIN InvoiceLine IL ON IL.TrackId=Tr.TrackId
JOIN Invoice I ON I.InvoiceId=IL.InvoiceId
GROUP BY Ar.Name
HAVING ts >= ALL (SELECT SUM(IL.UnitPrice) AS ts
FROM Artist Ar
JOIN Album Al ON Ar.ArtistId=Al.ArtistId
JOIN Track Tr ON Al.AlbumId=Tr.AlbumId
JOIN InvoiceLine IL ON IL.TrackId=Tr.TrackId
JOIN Invoice I ON I.InvoiceId=IL.InvoiceId
GROUP BY Ar.Name)


I am using DB Browser for SQLite and when I run the query it says
near "ALL" syntax error:

This query should work on Chinook Database, the schema is in this link

Thank you.

vkp vkp
Answer

You can use order by and limit. This would work when there are no ties for the highest sales.

SELECT Ar.Name, SUM(IL.UnitPrice) AS ts
FROM Artist Ar
    JOIN Album Al ON Ar.ArtistId=Al.ArtistId
    JOIN Track Tr ON Al.AlbumId=Tr.AlbumId
    JOIN InvoiceLine IL ON IL.TrackId=Tr.TrackId
    JOIN Invoice I ON I.InvoiceId=IL.InvoiceId
GROUP BY Ar.Name
ORDER BY ts DESC LIMIT 1

Edit: To get all the maximum sales in case of ties.

select t1.name from
(SELECT Ar.Name, SUM(IL.UnitPrice) AS ts
FROM Artist Ar
    JOIN Album Al ON Ar.ArtistId=Al.ArtistId
    JOIN Track Tr ON Al.AlbumId=Tr.AlbumId
    JOIN InvoiceLine IL ON IL.TrackId=Tr.TrackId
    JOIN Invoice I ON I.InvoiceId=IL.InvoiceId
    GROUP BY Ar.Name) t1
JOIN (select max(ts) as maxsale from
          (SELECT Ar.Name, SUM(IL.UnitPrice) AS ts
           FROM Artist Ar
           JOIN Album Al ON Ar.ArtistId=Al.ArtistId
           JOIN Track Tr ON Al.AlbumId=Tr.AlbumId
           JOIN InvoiceLine IL ON IL.TrackId=Tr.TrackId
           JOIN Invoice I ON I.InvoiceId=IL.InvoiceId
           GROUP BY Ar.Name) t) totals 
ON totals.maxsale = t1.ts