I am trying to calculate a pct of total in a query. The example below uses Adventure works db and what I am trying to do is calculate a percentage of ttl sales for each item in the territory.
So Item A has a total Sales of 100 Territory x sold 10 of the 100 A Items so the pct of ttl calculation should be 10% or .1
Here is the Adventure Works DB Simulation
where productid = 777 --all sales = 678
select SUM(d.orderqty)ITEM_TRTRY_SLS, (SUM(d.orderqty) /(select i.ITEM_TRTRY_SLS from #ITEMSALES i where i.ProductID = p.ProductID)) as pct_of_ttl, p.ProductID, h.TerritoryID from Sales.SalesOrderHeader h join Sales.SalesOrderDetail d on h.salesorderid = d.salesorderid join Production.Product p on p.ProductID = d.ProductID
where p.productid = 777 group by p.ProductID, h.TerritoryID -- territory 1 should be .28466