CVS Health is trying to better understand its
pharmacy sales, and how well different products are selling. Each drug can only
be produced by one manufacturer.
Write a query to find the total sales of drugs
for each manufacturer. Round your answer to the closest million, and report
your results in descending order of total sales.
Because this data is being directly fed into a
dashboard which is being seen by business stakeholders, format your result like
this: "$36 million".
table name:
Solution:
select manufacturer, concat('$',format(round(sum(total_sales)/1000000,0 ),'N0'),' million') as sale
from pharmacy_sales group by manufacturer
order by sum(total_sales) desc
Output:
SQL Script:
CREATE TABLE [dbo].[pharmacy_sales](
[product_id]
[int] NULL,
[units_sold]
[int] NULL,
[total_sales]
[decimal](18, 2) NULL,
[cogs]
[decimal](18, 2) NULL,
[manufacturer]
[varchar](50) NULL,
[drug]
[varchar](50) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (94, 132362, CAST(2041758.41 AS Decimal(18, 2)), CAST(1373721.70 AS Decimal(18, 2)), N'Biogen', N'UP and UP')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (9, 37410, CAST(293452.54 AS Decimal(18, 2)), CAST(208876.01 AS Decimal(18, 2)), N'Eli Lilly', N'Zyprexa')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (50, 90484, CAST(2521023.73 AS Decimal(18, 2)), CAST(2742445.90 AS Decimal(18, 2)), N'Eli Lilly', N'Dermasorb')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (61, 77023, CAST(500101.61 AS Decimal(18, 2)), CAST(419174.97 AS Decimal(18, 2)), N'Biogen', N'Varicose
Relief')
GO
INSERT [dbo].[pharmacy_sales] ([product_id], [units_sold], [total_sales], [cogs], [manufacturer], [drug]) VALUES (136, 144814, CAST(1084258.00 AS Decimal(18, 2)), CAST(1006447.73 AS Decimal(18, 2)), N'Biogen', N'Burkhart')
GO