Get the Percent of Total % of Sales of the Product category to Over All Sales

We need to create a report which shows the percentage of the total sales of the product category.

For the Demo we are using the AdventureWorksDW Database.

Below is the SQL Script.

SELECT pc.englishproductcategoryname                                  AS        [Product Category],        Sum(f.salesamount)                                             AS        [Total Sales Amount],        Cast(100.00 * Sum(f.salesamount) / Sum(Sum(f.salesamount))                                             OVER() AS DECIMAL(10, 3)) AS        [% Of Total Sales] FROM   factresellersales f        JOIN dimproduct p          ON f.productkey = p.productkey        JOIN dimproductsubcategory ps          ON ps.productsubcategorykey = p.productsubcategorykey        JOIN dimproductcategory pc          ON pc.productcategorykey = ps.productcategorykey GROUP  BY pc.englishproductcategoryname ORDER  BY [% of total sales] DESC

Running this Script  

Below script to drill down the Product Category to product sub category.

SELECT ps.EnglishProductSubcategoryName                               AS        [Product Sub Category],        Sum(f.salesamount)                                             AS        [Total Sales Amount],        Cast(100.00 * Sum(f.salesamount) / Sum(Sum(f.salesamount))                                             OVER() AS DECIMAL(10, 3)) AS        [% Of Total Sales] FROM   factresellersales f        JOIN dimproduct p          ON f.productkey = p.productkey        JOIN dimproductsubcategory ps          ON ps.productsubcategorykey = p.productsubcategorykey        JOIN dimproductcategory pc          ON pc.productcategorykey = ps.productcategorykey where pc.englishproductcategoryname='Bikes' GROUP  BY ps.EnglishProductSubcategoryName ORDER  BY [% of total sales] DESC

 

Drilling down the product sub category to product

SELECT p.englishproductname                                        Product,        Sum(salesamount)                                            AS        [Total Sales Amount],        Cast(100.00 * Sum(salesamount) / Sum(Sum(salesamount))                                           OVER() AS DECIMAL(10, 3)) AS        [% of total sales] FROM   dbo.factresellersales f        JOIN dbo.dimreseller r          ON f.resellerkey = r.resellerkey        JOIN dbo.dimproduct p          ON f.productkey = p.productkey        JOIN dimproductsubcategory ps          ON ps.productsubcategorykey = p.productsubcategorykey WHERE  ps.englishproductsubcategoryname = 'Road Bikes' GROUP  BY englishproductname ORDER  BY [% of total sales] DESC

Running this script.