Calculating Percentages for Each Row

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Calculating Percentages for Each Row

Author

Topic

Paul Skinner
Starting Member

9 Posts

Posted - 2007-12-10 : 10:39:33

One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:

(Ignore that the ERD is in Access, the database is SQL Server 2005)

How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?

What I'm aiming for is each row of products is shown, with its percentage of the whole sales.

AVG is involved this time, isn't it?

The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask

Thank you in advance for any replies.

Paul

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-10 : 10:45:07

SELECT	p.productID, 
	p.total_qty,
	percentage	= p.total_qty * 100.0 / t.total_qty
FROM
(
	SELECT 	p.productID, total_qty = SUM(oc.quantity)
	FROM   	products p
		INNER JOIN orderContents oc	ON	p.productID	= oc.productID
	GROUP BY p.productID
) p
CROSS JOIN
(
	SELECT	total_qty = SUM(oc.quantity)
	FROM   	products p
		INNER JOIN orderContents oc	ON	p.productID	= oc.productID
) t

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2007-12-10 : 10:47:24

by value?

select o.productID, value = sum(o.quantity * productPrice), pct = 100.0 * sum(o.quantity * productPrice) / max(tot.totvalue)
from orderContents o
join products p
on o.productID = o.ProductID
cross join (select totvalue = sum(o.quantity * productPrice)
from orderContents o
join products p
on o.productID = o.ProductID
) tot
group by o.productID

khtans is more colourful.
And he types a lot more quickly.
Depends on whether you want quentity or value.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-12-10 : 10:53:22

If you are using SQL 2005, you can also use the OVER() clause, it's a little shorter and easier to work with:

http://www.sqlteam.com/article/sql-sever-2005-using-over-with-aggregate-functions

- Jeff
http://weblogs.sqlteam.com/JeffS

Paul Skinner
Starting Member

9 Posts

Posted - 2007-12-10 : 11:12:23

Thanks khtan. That's what I was looking for.

nr: that's an interesting way of looking at it. I hadn't thought of that. Thank you also.

jsmith8858: I have SQL 2005 here, but a lowly 2000 implementation where this will one day be deployed once I've sorted it properly. Thanks anyway.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-10 : 20:14:46

actually in this case, percentage by quantity or value will give the same result as the product price is same regardless of order. It does make a difference if the price may be varies depending on the order. In such case, percentage by value might be a better presentation of the information. Or may not. It all depends on what user wants to see

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources