combine two sqlite queries that work separately-CodePudding

I am working on the basic chinook database, and am trying to write a sqlite query to create a view called v10BestSellingArtists for the 10 bestselling artists based on the total quantity of tracks sold (named as TotalTrackSales) order by TotalTrackSales in descending order. TotalAlbum is the number of albums with tracks sold for each artist.

I can write queries for both of them separately, but I can't figure out how to merge these two queries:

query for finding Totaltracksales:

Select 
r.name as artist, 
count (i.quantity) as TotalTrackSales 
from albums a 
left join tracks t on t.albumid == a.albumid 
left join invoice_items i on i.trackid == t.trackid 
left join artists r on a.artistid == r.artistid 
group by r.artistid 
order by 2 desc 
limit 10;

and the second query for totalAlbum :

Select 
r.name as artist, 
count(a.artistId) from albums a 
left join artists r where a.artistid == r.artistid 
group by a.artistid 
order by 2 desc 
limit 10;

but I want one query with the columns: Artist, TotalAlbum TotalTrackSales.

Any help is appreciated.

The schema for the album table:

 [Title] NVARCHAR(160)  NOT NULL,
 [ArtistId] INTEGER  NOT NULL,
 FOREIGN KEY ([ArtistId]) REFERENCES "artists" ([ArtistId])

artists table :

[ArtistId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
[Name] NVARCHAR(120)

tracks table schema:

 [TrackId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [Name] NVARCHAR(200)  NOT NULL,
    [AlbumId] INTEGER,
    [MediaTypeId] INTEGER  NOT NULL,
    [GenreId] INTEGER,
    [Composer] NVARCHAR(220),
    [Milliseconds] INTEGER  NOT NULL,
    [Bytes] INTEGER,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    FOREIGN KEY ([AlbumId]) REFERENCES "albums" ([AlbumId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([GenreId]) REFERENCES "genres" ([GenreId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([MediaTypeId]) REFERENCES "media_types" ([MediaTypeId])
                ON DELETE NO ACTION ON UPDATE NO ACTION

table invoice_items:

    [InvoiceLineId] INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    [InvoiceId] INTEGER  NOT NULL,
    [TrackId] INTEGER  NOT NULL,
    [UnitPrice] NUMERIC(10,2)  NOT NULL,
    [Quantity] INTEGER  NOT NULL,
    FOREIGN KEY ([InvoiceId]) REFERENCES "invoices" ([InvoiceId])
                ON DELETE NO ACTION ON UPDATE NO ACTION,
    FOREIGN KEY ([TrackId]) REFERENCES "tracks" ([TrackId])
                ON DELETE NO ACTION ON UPDATE NO ACTION

CodePudding user response：

Just to merge your 2 queries, you can do the following using CTE:

with total_track_sales as (
Select 
r.name as artist, 
count (i.quantity) as TotalTrackSales 
from albums a 
left join tracks t on t.albumid == a.albumid 
left join invoice_items i on i.trackid == t.trackid 
left join artists r on a.artistid == r.artistid 
group by r.artistid 
order by 2 desc 
limit 10 ),
with total_album as (
Select 
r.name as artist, 
count(a.artistId) as TotalAlbums from albums a 
left join artists r where a.artistid == r.artistid 
group by a.artistid 
order by 2 desc 
limit 10 )

select artist, TotalTrackSales, TotalAlbums
from total_track_sales ts inner join total_album ta
on ts.artist = ta.artist

CodePudding user response：

You can try a single query using DISTINCT and combining aggregates and window functions.

select * 
from (
   select 
      r.name as artist, 
      count (i.quantity) as TotalTrackSales,
      row_number() over (order by count (i.quantity) desc) rnT,
      count (distinct a.albumid) as totalAlbums,
      row_number() over (order by count (distinct a.albumid) desc) rnA,
   from albums a 
   left join tracks t on t.albumid == a.albumid 
   left join invoice_items i on i.trackid == t.trackid 
   left join artists r on a.artistid == r.artistid 
   group by r.artistid 
)
where rnT <= 10 or rnA <= 10