SQL Exercise -16

SQL Exercise:

Write an SQL query to calculate the percentage contribution to sales by each manager for a company. The data is stored in a table named Sales. The query should display the ManagerName, TotalSales for each manager, and their percentage contribution to the total company sales.

Create table script:

CREATE TABLE Sales (
ManagerName VARCHAR(50),
SalesAmount INT
);

 

Data insertion script:

INSERT INTO Sales (ManagerName, SalesAmount) VALUES 
('Anil', 40000),
('Priya', 25000),
('Ramesh', 15000),
('Priya', 30000),
('Anil', 20000),
('Ramesh', 10000);

Solution:

SELECT 
ManagerName,
SUM(SalesAmount) AS TotalSales,
CAST((SUM(SalesAmount) * 100.0) / SUM(SUM(SalesAmount)) OVER () AS DECIMAL(9,2)) AS PercentageContribution
FROM 
Sales
GROUP BY 
ManagerName
ORDER BY 
PercentageContribution DESC;

Output:

Explanation:

• SUM(SalesAmount):
  • Calculates the total sales for each manager within their group.
  • Used as SUM(SalesAmount).
• SUM(SUM(SalesAmount)) OVER ():
  • Calculates the total sales across all managers using a window function.
  • OVER () ensures this runs across the entire dataset without grouping.
• CAST((SUM(SalesAmount) * 100.0) / SUM(SUM(SalesAmount)) OVER (), decimal(9,2)):
  • Formula to calculate the percentage contribution for each manager
  • Ensures that results is displayed two places of decimal.
• GROUP BY ManagerName:
  • Groups the data by ManagerName so that total sales per manager can be aggregated.
• ORDER BY PercentageContribution DESC:
  • Sorts the results in descending order of percentage contribution.