Changeset 4abe330 for music/views/rank_list_artists.sql

Timestamp:

05/01/25 12:32:20 (4 weeks ago)

Author:

ManuelTrajcev <manueltrajcev7@…>

Branches:

master

Children:

59b2e9c

Parents:

611686e

Message:

rank_list_artists view update + bootstrap styling

File:

• music/views/rank_list_artists.sql (modified) (1 diff)

music/views/rank_list_artists.sql

@@ -1 +1 @@
 CREATE VIEW rank_list_artists AS
-    SELECT
-            (ar.name),
-            count(il.invoice_line_id)
-as num_invoices
-    FROM artist ar
-    left join album al on ar.artist_id = al.album_id
-    left join track tr on al.album_id = tr.album_id
-    left join invoice_line il on tr.track_id = il.track_id
-    group by ar.name
-    order by num_invoices desc
+SELECT ar.name,
+       count(il.invoice_line_id) AS num_invoices,
+       COALESCE(SUM(i.total), 0) AS money_earned
+FROM artist ar
+         LEFT JOIN album al ON ar.artist_id = al.album_id
+         LEFT JOIN track tr ON al.album_id = tr.album_id
+         LEFT JOIN invoice_line il ON tr.track_id = il.track_id
+         LEFT JOIN invoice i on il.invoice_id = i.invoice_id
+GROUP BY ar.name
+ORDER BY money_earned DESC;