Objective

Write a SQL query to calculate the total sales revenue for each manufacturer from the pharmacy sales data.

Table Schema:

• pharmacy_sales

Task Requirements:

• Calculate total sales for each manufacturer
• Round the total to the nearest million
• Format as "$X million" (e.g., "$105 million")
• Sort by total sales descending, then alphabetically by manufacturer for ties
  
  

Output columns: manufacturer, sale

SELECT 
    manufacturer,
    CONCAT('$', ROUND(SUM(total_sales) / 1000000), ' million') AS sale
FROM pharmacy_sales
GROUP BY manufacturer
ORDER BY SUM(total_sales) DESC, manufacturer ASC;

Explanation

Step 1: Aggregating Sales by Manufacturer

GROUP BY manufacturer

Groups all sales records by manufacturer; SUM(total_sales) calculates total revenue for each manufacturer.

Step 2: Converting to Millions

ROUND(SUM(total_sales) / 1000000)

Dividing by 1,000,000 converts to millions; ROUND() removes decimal places for cleaner presentation.

Step 3: Formatting the Output

CONCAT('$', ROUND(SUM(total_sales) / 1000000), ' million') AS sale

Combines the dollar sign, rounded million value, and " million" text to produce a business-friendly format like "$105 million".

Step 4: Sorting the Results

ORDER BY SUM(total_sales) DESC, manufacturer ASC

Sorts by total sales descending (highest revenue first); ties broken alphabetically by manufacturer name.