Grouping Sum Amount/Total Sum Amount

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

SQL Server 2000 Forums

Transact-SQL (2000)

Grouping Sum Amount/Total Sum Amount

Author

Topic

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-11-06 : 14:59:51

I currently have the following:

SELECT ISNULL(Babr, 'other') AS St, SicDescription, SUM(AccountMV) AS mv
FROM dbo.SnapsRaw
WHERE (MonthEnd = '08/01/2006')
GROUP BY Babr, SicDescription
ORDER BY Babr

Results Look like this:

FL | Amusement |2641940.7000
FL | Engineering | 82802330.4000
IN | Museums | 13418262.3900
IN | Electric | 1141768.8400

I would like to add a column that gives me the % of the overall marketvalue for the particular state, example:

FL | Amusement |2641940.7000 | 20%
FL | Engineering | 82802330.4000 | 2%
IN | Museums | 13418262.3900 | 3%
IN | Electric | 1141768.8400 |19%

The number in which i need to divide by would be built from

select branchstate, sum(accountmv) from snapsraw where monthend = '08/01/2006' group by branchstate

Results look like this:
Florida 1990129983.4800
Indiana 962445383.5500

How do i get the % number to show up in the extra column, making sure it divides the correct states overall market value. Apologizes if this sounds confusing, trying to phrase question best I can

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-06 : 15:32:25

Try a INNER JOIN with a derived table like
SELECT State, SUM(MarketValue) smv from ... gropu by State

And then in the select, divide by derived table's smv column.

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-11-07 : 08:22:45

Appreciate the reply and advice, somewhat new to the SQL world, could you expand upon what you said? I guess i'm confused on how I will divide each states market value and category by the overall market value for the particular state, is that where the inner join comes in?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 08:28:24

Something like this

SELECT		ISNULL(s.Babr, 'other') AS St,
		s.SicDescription,
		SUM(s.AccountMV) AS mv,
		SUM(s.AccountMV) / q.ps as Percentile
FROM		dbo.SnapsRaw s
INNER JOIN	(
			select		branchstate,
					sum(accountmv) ps
			from		snapsraw
			where		monthend = '08/01/2006'
			group by	branchstate 
		) q on q.branschstate = s.babr
WHERE		s.MonthEnd = '08/01/2006'
GROUP BY	s.Babr,
		s.SicDescription
ORDER BY	s.Babr

It it does not work, please try to tweak the query before asking again...

Peter Larsson
Helsingborg, Sweden

duhaas
Constraint Violating Yak Guru

310 Posts

Posted - 2006-11-07 : 08:39:14

WOW, I cant thank you enough, I really appreciate you spending the time and assisting me with his.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-07 : 08:49:41

You could also try to use COMPUTE keyword.

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources