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?