calculate total per group - 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

SQL Server 2000 Forums

Transact-SQL (2000)

calculate total per group

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2005-02-07 : 09:17:03

Hi,

I wrote a sp that returns the number of students per school for a particular region.
The result that I get is similiar to the following
Total School Region
23 XYZ 28
12 LBS 28
10 CDE 32
10 GHK 32

However, now I have to calculate the % for each school. So if Region 28 the overall total is 35 I need to calculate what percentage total 23 (school XYZ) is of 35.
I am not sure how to calculate the percentage in sql.
Below is part of my sp :
SELECT * INTO #tR FROM (
select learningstuds.sid as lsid,
learningstuds.misgeret as misgeret,
learningstuds.hasamaguf as hasamaguf,
learningstuds.mid as mid,
learningstuds.departure as departure,
student.family as familyStat,
student.region as region,
student.sOrigin as Sorigin,
student.studyState as studyState,
student.attitude as attitude,
student.behave as behave,
student.enrolltype as enrolltype,
tblmisgeret.sugmosad as sugmosad,
tblguf.Name as gufname
from learningstuds
inner join student on student.sid=learningstuds.sid
inner join tblguf on pnimcode=hasamaguf
inner join tblmisgeret on misgeretcode=misgeret

) t
where
departure is null

IF @param=1
Begin

select count ( *) as total,valuedesc,hasamaguf,gufname
from #tR
INNER JOIN tblddlbvalue ON familyStat= tblddlbvalue.valuecode
group by valuedesc,hasamaguf,gufname
order by hasamaguf

End

Thanks in advance for the help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-02-07 : 11:31:13

try something like this:

select t1.total, t1.total/(select sum(total) from MyTable group By Region where school = t1.school) as Percentage, ...
from MyTable t1
where...

Go with the flow & have fun! Else fight the flow

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources