Back to questions
CVS Health wants to gain a clearer understanding of its pharmacy sales and the performance of various products.
Write a query to calculate the total drug sales for each manufacturer. Round the answer to the nearest million and report your results in descending order of total sales. In case of any duplicates, sort them alphabetically by the manufacturer name.
Since this data will be displayed on a dashboard viewed by business stakeholders, please format your results as follows: "$36 million".
If you like this question, try out Pharmacy Analytics (Part 4)!
|94||132362||2041758.41||1373721.70||Biogen||UP and UP|
|Eli Lilly||$3 million|
The total sales for Biogen is $4 million ($2,041,758.41 + $500,101.61 + $1,084,258.00 = $3,626,118.02) and for Eli Lilly is $3 million ($293,452.54 + $2,521,023.73 = $2,814,476.27).
The dataset you are querying against may have different input & output - this is just an example!
To determine the total sales for each manufacturer, we use the aggregate function to calculate the sum of total sales and group the results by the manufacturer using the clause.
Here's the output of two randomly selected records:
The output above shows that Eli Lilly sold drugs with a total sales value of $81,641,381.27, while Biogen sold drugs with a total sales value of $69,824,472.58.
Eli Lilly's sales value of $81,641,381.27 is rounded to 82 million, while Biogen's sales value of \69,824,472.58 is rounded to $70 million.
To format the sales data as "" symbol, the in million, and the " million" string. A space is added in front of "million" to match the desired format.
P.S. It is not necessary to convert into data type as the function accepts both and data types. Bear in mind that the column is now a data type.
|Eli Lilly||$82 million|
The sales values are now formatted as "$xx million" for display on the dashboard.
Solution #2: Using CTE
Learn all about CTEs in this tutorial!