Calculate percentage column help!!

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

SQL Server Development (2000)

Calculate percentage column help!!

Author

Topic

olud
Starting Member

6 Posts

Posted - 2003-04-23 : 08:15:38

Would someone please help me out here as i am pulling my hair out on this one.
I have used the code below to create a of a table in an sql2000 database

create view dbo.win AS
SELECT ' AST' AS TypeOfPost, COUNT(*) AS Total
FROM dbo.LTV_TypeOfPost
WHERE (TypeOfPost = 'AST') AND (TypeOfSchool = 'Primary') AND (Lea = 'Barking and Dagenham')
UNION ALL
SELECT ' Deputy Head Teacher' AS TypeOfPost, COUNT(*) AS Total
FROM dbo.LTV_TypeOfPost
WHERE (TypeOfPost = 'Deputy Head Teacher') AND (TypeOfSchool = 'Primary') AND (Lea = 'Barking and Dagenham')
UNION ALL
SELECT 'Head Teacher' AS TypeOfPost, COUNT(*) AS Total
FROM dbo.LTV_TypeOfPost
WHERE (TypeOfPost = 'Head Teacher') AND (TypeOfSchool = 'Primary') AND (Lea = 'Barking and Dagenham')

the above code creates the following view;

TypeOfPost Total
AST 1
Deputy Head Teacher 3
Head teache 7

My problem is that i would like to create an additional column in the view/table that will calculate the percentage (%) for each row eg

TypeOfPost Total Percentage
AST 1 9.09%
Deputy Head Teacher 3 27.27%
Head teache 7 63.63%

Thanks in advance for your help

Olud

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 08:24:41

First off, rewrite your code like this:

SELECT TypeOfPost, COUNT(*) AS Total
FROM dbo.LTV_TypeOfPost
WHERE TypeOfSchool = 'Primary' and Lea = 'Barking and Dagenham'
GROUP By TypeOfPost

Read about GROUP BY in Books on-line.

For the totals column, then, you can do it like this:

SELECT TypeOfPost, COUNT(*) AS Total,
1.0 * COUNT(*) / (SELECT COUNT(*) FROM dbo.LTV_TypeOfPost WHERE TypeOfSchool = 'Primary' and Lea = 'Barking and Dagenham')
As Pct
FROM dbo.LTV_TypeOfPost
WHERE TypeOfSchool = 'Primary' and Lea = 'Barking and Dagenham'
GROUP By TypeOfPost

Add to the WHERE clause in either case if you want only those 3 types of Posts. For example:

WHERE TypeOfSchool = 'Primary' and Lea = 'Barking and Dagenham' and
TypeOfPost in ('AST','Deputity Head Teacher','Head Teacher')

Finally, it appeared that you were putting spaces in front of two types of posts (sorting reasons?) To do that, edit your select clause:

SELECT CASE TypeOfPost WHEN IN ('AST','Deputity Head Teacher') THEN ' ' ELSE '' END + TypeOfPost as TypeOfPost, COUNT(*) as Total, ...etc ...

Look up CASE in Books-on-line as well for more information about that one; it allows you to do IF-THEN's.

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-23 : 08:26:28

First this will give you the same results

SELECT TypeOfPost, COUNT(*) AS Total,
FROM dbo.LTV_TypeOfPost
WHERE (TypeOfSchool = 'Primary') AND (Lea = 'Barking and Dagenham')
GROUP BY TypeOfPost

Jeff's got it.

Edited by - ValterBorges on 04/23/2003 08:27:13

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-04-23 : 14:25:18

Great minds think alike, Valter!

- Jeff

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-04-23 : 22:02:18

olud
Starting Member

6 Posts

Posted - 2003-04-24 : 05:53:43

Jeff and Valter...dont know what to say except that you guys are just great!!!...thanks a million .Both solutions solved the problem
THANK YOU>>>>>THANK YOU>>>>THANK YOU!!!!!!

Olud

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources