How to divide count by count and add percentage?

Question

0.00/5 (No votes)

See more:

I have a table with the following data:

I want to create a stored procedure to count the records where flag = 1 based on the Id as an input

So in this example for Id = 1, the count will be 3
then i want to divide it by all records for this Id =1 regardless if the flag is 1 or 0
so in this case it will be 3/5 = 0.6
then i want to multiply 0.6*100 and add the % percentage to the value
which is 60% (this value should be retrieved in my stored precedure)
then i want to say:

if value <= 20 return bad
else if value >20 and <=40 return poor
else if value >40 and <=60 return good
else if value >60 and <=80 return good
else if value >80 and <=100 return perfect

So my stored procedure should return the percentage and the description
in my example here i should get
60% and good

What I have tried:

Create PROCEDURE [dbo].[usp_GetRating]
		@Id int
AS

declare @ating int;

select @Rating = count(*) From Table
	where Id = @Id and Flag= 1 / select count(*) From Table
	where Id = @Id


RETURN 0

I'm getting an error when dividing by count, also how to add the perecent and description?

Posted 10-May-21 2:27am

Member 14800672

Updated 10-May-21 3:57am

Add a Solution

Comments

Richard MacCutchan 10-May-21 8:55am

You should add a check to make sure the returned count is not zero.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

jsc42 · Answer 1 · 2021-05-10T03:00:00

The 2nd select count should be in parentheses to indicate that it is a sub query, not a totally separate query split from the first using '/' (which is wrong, it should be ';').

As a general principle, if you are subquerying on the same table as the one that it is a part of, you should look to see if it is necessary. Try something like

SELECT @Rating = COUNT(*) / SUM(Flag)
FROM Table
WHERE Id = @Id

This assumes that 0 and 1 are the only values in Flag.
If there are other values, replace
SUM(Flag)
with SUM(IF(Flag = 1, 1, 0))
or with COUNT(IF(Flag = 1, 1, NULL))

You can look at the other parts once you have got this part working

OriginalGriff · Answer 2 · 2021-05-10T03:02:00

Solution 2

Try something like

SQL

SELECT (SELECT COUNT(*) FROM MyTable WHERE ID = 1 AND Flag = 1) * 100 / 
       (SELECT COUNT(*) FROM MyTable WHERE ID = 1)

But ... do remember that COUNT returns an INTEGER, so you may need to cast it to a FLOAT to get an accurate value.

Posted 10-May-21 3:02am

OriginalGriff

Comments

Member 14800672 10-May-21 9:58am

Hi,

I've tried this
select cast((select count(*) From MyTable
where Id =@Id and Flag= 1) as float) / cast((select count(*) From MyTable
where Id =@Id ) as float) *100

but now i'm getting value like
66.6666666666667
How can i make it 66.7?

and then how to decide this based on the value retrieved
if value <= 20 return bad
else if value >20 and <=40 return poor
else if value >40 and <=60 return good
else if value >60 and <=80 return good
else if value >80 and <=100 return perfect

I want to get 66.7% and good from my stored procedure

OriginalGriff 10-May-21 10:43am

Try this:

https://www.w3schools.com/sql/func_sqlserver_round.asp