Business Question / task 3

Q&A DQ Courses
, , ,
#1

Hi!

https://app.dataquest.io/m/191/guided-project%3A-answering-business-questions-using-sql/3/selecting-albums-to-purchase

My Code:

WITH usa_sale AS
                      (
                 SELECT * , 
                                il.quantity AS purchase
                    FROM invoice i
             LEFT JOIN invoice_line il
                           on il.invoice_id = i.invoice_id
                  WHERE i.billing_country = "USA"
             ORDER BY customer_id
                      ),
usa_sale_genre AS
                             (
                 SELECT *, g.name Genre
                   FROM usa_sale u            
            LEFT JOIN track t
                         ON t.track_id = u.track_id
            LEFT JOIN genre g
                        ON g.genre_id = t.genre_id
                            )
    SELECT Genre, 
                   SUM(purchase) AS Purchase, 
                   (ROUND((CAST(COUNT(quantity) AS FLOAT) / 1040) , 3) * 100) || "%" AS "%_total_p"
       FROM usa_sale_genre u
    WHERE Genre = "Hip Hop/Rap"
            OR Genre = "Alternative & Punk"
            OR Genre = "Pop"
            OR Genre = "Blues"
GROUP BY genre
ORDER BY Purchase DESC

I think i got the result but i dont how to get a percentage in automatic way. I mind i want to get the percentage of total quantity using a subquery and not a mecanic way that i used above

Some one could give a hand?

Replace this line with the output/error
#2

hi @francisco.manhood

short question - how did you know total value is 1040?

If you used a query to get that, use the same query as a subquery to get the total quantity.

Basically your query would look something like this:

your query:

SELECT Genre, 
                   SUM(purchase) AS Purchase, 
                   (ROUND((CAST(COUNT(quantity) AS FLOAT) / 1040) , 3) * 100) || "%" AS "%_total_p"

modified with sub-query:

SELECT Genre, 
                   SUM(purchase) AS Purchase, 
                   (ROUND((CAST(COUNT(quantity) AS FLOAT) / (SELECT COUNT(*) FROM some_table ) ) , 3) * 100) || "%" AS "%_total_p"

The count(*) gives us the total number of records present in that table [some_table is dummy name].

1040 will get replaced with the sub-query.