15,038,345 members
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
Richard MacCutchan 10-May-21 8:55am

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

## Solution 1

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

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