Not getting 0 value in SQL count aggregate by inner join-CodePudding

I am using the basic chinook database and I am trying to get a query that will display the worst selling genres. I am mostly getting the answer, however there is one genre 'Opera' that has 0 sales, but the query result is ignoring that and moving on to the next lowest non-zero value.

I tried using left join instead of inner join but that returns different values.

This is my query currently:

create view max 
as 
    select distinct 
        t1.name as genre,
        count(*) as Sales 
    from 
        tracks t2 
    inner join 
        invoice_items t3 on t2.trackid == t3.trackid 
    left join 
        genres as t1 on t1.genreid == t2.genreid 
    group by 
        t1.genreid 
    order by 
        2 
    limit 10;

The result however skips past the opera value which is 0 sales. How can I include that? I tried using left join but it yields different results.

Any help is appreciated.

CodePudding user response：

If you want to include genres with no sales then you should start the joins from genres and then do LEFT joins to the other tables.
Also, you should not use count(*) which counts any row in the resultset.

SELECT g.name Genre, 
       COUNT(i.trackid) Sales 
FROM genres g 
LEFT JOIN tracks t ON t.genreid = g.genreid 
LEFT JOIN invoice_items i ON i.trackid = t.trackid 
GROUP BY g.genreid
ORDER BY Sales LIMIT 10;

There is no need for the keyword DISTINCT, since the query returns 1 row for each genre.

CodePudding user response：

When asking for the top n one must always state how to deal with ties. If I am looking for the top 1, but there are three rows in the table, all with the same value, shall I select 3 rows? Zero rows? One row arbitrarily chosen? Most often we don't want arbitrary results, which excludes the last option. This excludes LIMIT, too, because LIMIT has no clause for ties in SQLite.

Here is an example with DENSE_RANK instead. You are looking for the worst selling genres, so we must probably look at the revenue per genre, which is the sum of price x quantity sold. In order to include genres without invoices (and maybe even without tracks?) we outer join this data to the genre table.

select total, genre_name
from
(
  select
    g.name as genre_name,
    coalesce(sum(ii.unit_price * ii.quantity), 0) as total
    dense_rank() over (order by coalesce(sum(ii.unit_price * ii.quantity), 0)) as rnk
  from genres g
  left join tracks t on t.genreid = g.genreid
  left join invoice_items ii on ii.trackid = t.trackid
  group by g.name
) aggregated
where rnk <= 10
order by total, genre_name;