# Calculate a PCT of TTL

 0 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 .1Here is the Adventure Works DB Simulation```use AdventureWorks select SUM(d.orderqty)ITEM_TRTRY_SLS, p.ProductID``` `````` into #ITEMSALES from Sales.SalesOrderHeader h join Sales.SalesOrderDetail d on h.salesorderid = d.salesorderid join Production.Product p on p.ProductID = d.ProductID ```````group by p.ProductID` ``````select * from #ITEMSALES ``````where productid = 777 --all sales = 678select 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.ProductIDwhere p.productid = 777 group by p.ProductID, h.TerritoryID -- territory 1 should be .28466![alt text][1] [1]: http:// more ▼ asked Nov 30, 2011 at 01:36 PM in Default siera_gld 1k ● 77 ● 82 ● 84 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

 0 ``````SELECT SUM(d.orderqty) [ITEM_TRTRY_SLS] ,1.0 * SUM(d.orderqty) / (SELECT 1.0 * SUM(d2.orderqty) FROM sales.SalesOrderDetail d2 WHERE d2.ProductID = d.ProductID) [PCT_OF_TTL] ,d.ProductID ,h.TerritoryID FROM Sales.SalesOrderHeader h JOIN Sales.SalesOrderDetail d ON (h.salesorderid = d.salesorderid) WHERE d.productid = 777 GROUP BY h.TerritoryID, d.ProductID `````` ORDER BY h.TerritoryID more ▼ answered Nov 30, 2011 at 05:14 PM Scot Hauder 6k ● 13 ● 15 ● 18 add new comment (comments are locked) 10|1200 characters needed characters left ▼ Viewable by all users

### New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

By Email:

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x25

asked: Nov 30, 2011 at 01:36 PM

Seen: 975 times

Last Updated: Nov 30, 2011 at 01:47 PM