Dividing Up Cost Based on % - SQL Server Forums

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

Dividing Up Cost Based on %

Author

Topic

dugoneill
Starting Member

11 Posts

Posted - 2012-07-23 : 14:41:47

I am trying to query to calculate the cost for each group when each group has a % of the total.

Group A needs 300 widgets
Group B needs 270 widgets
Group C needs 60 widgets
Group D needs 30 widgets
Group E needs 220 widgets
Group F needs 50 widgets
Group G needs 330 widgets
Group H needs 1750 widgets
Group I needs 100 widgets
Group J needs 1110 widgets

Bulk buy of 4,220 Widgets is $75,055.89, so need to calculate the cost per Group.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 14:46:06

One way, if you are on SQL 2005 or higher and if you have the data in a table, is like this:

SELECT
	GroupName,
	Widgets,
	75055.89*Widgets/SUM(Widgets) OVER() AS CostPerGroup
FROM
	YourTableWhichHasGroupNamesAndWidgets;

dugoneill
Starting Member

11 Posts

Posted - 2012-07-23 : 14:59:11

quote:
Originally posted by sunitabeck

One way, if you are on SQL 2005 or higher and if you have the data in a table, is like this:
SELECT
GroupName,
Widgets,
75055.89*Widgets/SUM(Widgets) OVER() AS CostPerGroup
FROM
YourTableWhichHasGroupNamesAndWidgets;

Thank you - much simpler than the hacks I was coming up with; but this comes out to too many decimal places, so this would need to be rounded to a dollar/cent amount for each making sure the total is still equal - could this be done with Convert(Decimal(5,2)?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 15:04:22

You are welcome. Yes, you should be able to use CAST or CONVERT. You can also use ROUND function, although I too prefer CAST or CONVERT to decimal.

dugoneill
Starting Member

11 Posts

Posted - 2012-07-23 : 15:18:21

quote:
Originally posted by sunitabeck

You are welcome. Yes, you should be able to use CAST or CONVERT. You can also use ROUND function, although I too prefer CAST or CONVERT to decimal.

Excellent - thanks again.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-07-23 : 15:29:04

You may want to use a higher precision that DECIMAL(5,2) because if your group cost happens to be $1,000 or more that would cause an overflow. I usually use precision of 9 or 19 or 28 or 38. In your case, DECIMAL (9,2) would suffice.

(The reason I use those 9,19,28,38 is because of the storage thresholds - the rationale being that, if I am going to use 5 bytes, I might as well specify the largest precision that can hold): http://msdn.microsoft.com/en-us/library/ms187746.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources