Calculate percentage of total count in SQL Server

irshad12...

SOLVED CLOSED ACTIVE ANSWERED
User: irshad1231
Posted: on Feb 20, 2019 02:04 AM
Forum: SQL Server
Answer: 2
Views: 2468

Dear All,

I have a survey questions with 5 options. I want to know how many users selected the option 1, option2 and so on till option 5.

I have built this query and i think it's not working for me.

Result Table:

SELECT TOP 1000 [Emp_id]
      ,[QID]
      ,[AnswerId]
  FROM ResultTable

My calculated query:

SELECT QuestionTable.QuestionDesc, 
COUNT(ResultTable_Organization.AnswerId) AS TotalAnswered,
SUM(CASE WHEN [AnswerId] = 5 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Disagree',
SUM(CASE WHEN [AnswerId] = 4 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS ' Strongly Disagree', SUM(CASE WHEN [AnswerId] = 3 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Agree', SUM(CASE WHEN [AnswerId] = 2 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Not Applicable', SUM(CASE WHEN [AnswerId] = 1 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),
COUNT(ResultTable_Organization.AnswerId)) AS 'Strongly Agree'
FROM ResultTable_Organization INNER JOIN
QuestionTable ON ResultTable_Organization.QID = QuestionTable.QID
WHERE (ResultTable_Organization.QID = 1)
GROUP BY QuestionTable.QuestionDesc

my table data

Emp_id	QID	AnswerId
1111	1	5
2222	1	5
3333	1	4
4444	1	5
5555	1	4
6666	1	5

Expected output:

If option 5 70% Strongly Agree

If option 4 5% Agree

If option 3 5% Neutral

If option 2 10% Disagree

If option 1 10% Strongly Disagree

Can you please let me know where i'm wrong.

Thanks

Download FREE API for Word, Excel and PDF in ASP.Net

dharmend...

0 4

Reply
ANSWER
User: dharmendr
Replied: on Feb 20, 2019 04:43 AM
Report

Hi irshad1231,

Check the below sample query.

SQL

DECLARE @ResultTable AS TABLE(Emp_id INT,QID INT,AnswerId INT)
INSERT INTO @ResultTable VALUES(1111,1,5)
INSERT INTO @ResultTable VALUES(2222,1,5)
INSERT INTO @ResultTable VALUES(3333,1,4)
INSERT INTO @ResultTable VALUES(4444,1,5)
INSERT INTO @ResultTable VALUES(5555,1,4)
INSERT INTO @ResultTable VALUES(6666,1,5)

SELECT QID,
CASE 
	WHEN AnswerId = 5 THEN COUNT(AnswerId) * 100 / (SELECT COUNT(*) FROM @ResultTable)
	WHEN AnswerId = 4 THEN COUNT(AnswerId) * 100 / (SELECT COUNT(*) FROM @ResultTable)
	WHEN AnswerId = 3 THEN COUNT(AnswerId) * 100 / (SELECT COUNT(*) FROM @ResultTable)
    WHEN AnswerId = 2 THEN COUNT(AnswerId) * 100 / (SELECT COUNT(*) FROM @ResultTable)
    ELSE COUNT(AnswerId) * 100 / (SELECT COUNT(*) FROM @ResultTable)
END 'Percentage',
CASE  
    WHEN AnswerId = 5 THEN 'Strongly Agree' 
    WHEN AnswerId = 4 THEN 'Agree' 
    WHEN AnswerId = 3 THEN 'Neutral' 
    WHEN AnswerId = 2 THEN 'Disagree' 
    ELSE 'Strongly Disagree' 
END 'Answer'
FROM @ResultTable 
GROUP BY AnswerId,QID

Output

QID	Percentage	Answer
1	33	Agree
1	66	Strongly Agree

dharmend...

0 7

Reply
ANSWER
User: dharmendr
Replied: on Feb 20, 2019 11:56 PM
Modified: on Feb 20, 2019 11:57 PM
Report

Use SQL CONVERT function.

Refer below sample test query.

SQL

DECLARE @ResultTable AS TABLE(Emp_id INT,QID INT,AnswerId INT)
INSERT INTO @ResultTable VALUES(1111,1,5)
INSERT INTO @ResultTable VALUES(2222,1,5)
INSERT INTO @ResultTable VALUES(3333,1,4)
INSERT INTO @ResultTable VALUES(4444,1,5)
INSERT INTO @ResultTable VALUES(5555,1,4)
INSERT INTO @ResultTable VALUES(6666,1,5)

SELECT ResultTable_Organization.QID,   
COUNT(ResultTable_Organization.AnswerId) AS TotalAnswered,
CONVERT(DECIMAL(10,2),SUM(CASE WHEN [AnswerId] = 5 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),COUNT(ResultTable_Organization.AnswerId))) AS 'Disagree',
CONVERT(DECIMAL(10,2),SUM(CASE WHEN [AnswerId] = 4 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),COUNT(ResultTable_Organization.AnswerId))) AS 'Strongly Disagree', 
CONVERT(DECIMAL(10,2),SUM(CASE WHEN [AnswerId] = 3 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),COUNT(ResultTable_Organization.AnswerId))) AS 'Agree', 
CONVERT(DECIMAL(10,2),SUM(CASE WHEN [AnswerId] = 2 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),COUNT(ResultTable_Organization.AnswerId))) AS 'Not Applicable', 
CONVERT(DECIMAL(10,2),SUM(CASE WHEN [AnswerId] = 1 THEN 1 ELSE 0 END) * 100 / CONVERT(decimal(10),COUNT(ResultTable_Organization.AnswerId))) AS 'Strongly Agree'
FROM @ResultTable ResultTable_Organization
WHERE (ResultTable_Organization.QID = 1)
GROUP BY ResultTable_Organization.QID

Output

QID	TotalAnswered	Disagree	Strongly Disagree	Agree	Not Applicable	Strongly Agree
1	6	66.67	33.33	0.00	0.00	0.00

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

Calculate percentage of total count in SQL Server

Company

Explore

Follow us

Contact Us