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.
| Author |
Topic |
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
 Posted - 2008-04-11 : 09:28:11
|
| I'm sure I should know the answer to this but it seems to be eluding me for some reason. I can blame the fact it's late on a friday if it helps.We have a simple table (in SQL Server 2000) with a structure like thisModeOfTravel | LocalIdentifier | RegionCar | 123456 | Yorkshirewalk | 123456 | Lancashirecar | 654321 | Cumbriawalk | 789789 | Cumbriawalk | 789789 | Cumbriawhat I am trying to get out of this is the Total numbers grouped by region (and mode of travel) and the percentage of the grand total by the same groupingi.e.Region | Mode | Total | percentageYorkshire | car | 1 | 20Lancashire | walk | 1 | 20Cumbria | car | 1 | 20Cumbria | walk | 2 | 40I can get the totals, just with a normal group by and a count, it's the percentages I am having trouble with. I'm sure I am missing something blindingly obvious but I can't see what and google isn't helping.Many thanks in advancesteve-----------ASCII and ye shall receive. |
|
|
harsh_athalye
Master Smack Fu Yak Hacker
5581 Posts |
Posted - 2008-04-11 : 09:37:35
|
| [code]declare @t table( ModeOfTravel varchar(50), LocalIdentifier int, Region varchar(50))insert @tselect 'Car',123456, 'Yorkshire' union allselect 'walk',123456, 'Lancashire' union allselect 'car',654321, 'Cumbria' union allselect 'walk',789789, 'Cumbria' union allselect 'walk',789789, 'Cumbria'select t1.region, t1.modeoftravel, t1.cnt, t1.cnt*1.0/t2.grn_total * 100 as percfrom(select region, modeoftravel, count(*) as cntfrom @tgroup by region, modeoftravel) t1cross join(select count(*) grn_total from @t) t2[/code]EDIT: Please add appropriate check to handle DIVIDE BY ZERO error.Harsh AthalyeIndia."The IMPOSSIBLE is often UNTRIED" |
 |
|
|
elwoos
Master Smack Fu Yak Hacker
2052 Posts |
Posted - 2008-04-11 : 09:47:41
|
| Harsh you are a star many thankssteve-----------ASCII and ye shall receive. |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2008-04-11 : 09:51:06
|
Here's an alternative...select region, modeoftravel, count(*) as cnt, 100.0 * count(*) / (select count(*) grn_total from @t) as cntfrom @tgroup by region, modeoftravel Ryan Randall Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|
|
Aggregate Issues