Calculate Percentage in relation to total records

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 Percentage in relation to total records

Author

Topic

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-13 : 13:46:28

I have a table that has the following columns:

Q1a, Q1b Q1c, Q2a, Q2b, Q2c, Q2d, Q2e etc etc

I want to create a query that returns each value entered for each of the questions, e.g. Q1a has possible values of Excellent, Good, Fair, Poor & N/A. I also want the query to return a percentage of the total records that represents each response if that makes sense and I have so far hit a brick wall on this.

As an example, the desired query would return the something like the following for each question:

Excellent 30%
Good 50%
Fair 10%
Poor 5%
N/A 5%

My query that I have started is as follows and just until I get it right just deals with 1 question:

SELECT Q1a, COUNT(Q1a) AS Q1aTotal
FROM dbo.tblQuestionnaireNonPurchased
GROUP BY Q1a

This returns data in the following format:

Q1a Q1aTotal
----------------------------
Excellent 7
Good 10
Fair 4
N/A 2

This gives me actual numbers but not the percentage of the total records.

Thanks in advance for your help.

Simon

www.creativenrg.co.uk

Tahsin
Starting Member

34 Posts

Posted - 2006-03-13 : 14:23:43

Try This:

SELECT Q1a, 
CAST(LEFT(CAST(COUNT(Q1a) AS NUMERIC)/(SELECT COUNT(*) FROM dbo.tblQuestionnaireNonPurchased)*100,2) AS VARCHAR) + '%' AS Q1aTotal
FROM dbo.tblQuestionnaireNonPurchased
GROUP BY Q1a

- Tahsin

CreativeNRG
Starting Member

44 Posts

Posted - 2006-03-13 : 14:39:54

quote:

Originally posted by Tahsin

Try This:

SELECT Q1a, 
CAST(LEFT(CAST(COUNT(Q1a) AS NUMERIC)/(SELECT COUNT(*) FROM dbo.tblQuestionnaireNonPurchased)*100,2) AS VARCHAR) + '%' AS Q1aTotal
FROM dbo.tblQuestionnaireNonPurchased
GROUP BY Q1a

- Tahsin

Fantastic - that works a treat. Thanks very much Tahsin.

www.creativenrg.co.uk

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources