x

Calculate a PCT of TTL

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

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 = 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

![alt text][1]

[1]: http://
more ▼

asked Nov 30, 2011 at 01:36 PM in Default

siera_gld gravatar image

siera_gld
1k 79 84 85

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first
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 gravatar image

Scot Hauder
6.1k 13 15 18

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

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.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x25

asked: Nov 30, 2011 at 01:36 PM

Seen: 1030 times

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