Click here to Skip to main content
15,032,967 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table with the following data:
Id Flag
1   0
1   0
1   1
1   1
1   1
2   1
2   1
3   0


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
Updated 10-May-21 3:57am
Comments
Richard MacCutchan 10-May-21 8:55am
   
You should add a check to make sure the returned count is not zero.

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
   
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.
   
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

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900