12,951,971 members (61,819 online)
Rate this:
See more:
Hi all

I have a table Code_Review
It is having 3 columns(pk_id,code_reviewer_id and rate)
There are 4 type of rating.
1-very good.
2-good.

I want to calculate how much rating given by each reviewer.
Means:
If Bhagirathi having id 200 has given 2 very good,4 good,3 bad and zero very bad rating to different code.

I want result
```Count(Rate)    Rate
2           1
4           2
3           3
0           4```

I have tried
`SELECT COUNT(RATE),RATE FROM CODE_REVIEW WHERE CODE_REVIEWER_ID= 200 GROUP BY RATE;`

It is showing result
```Count(Rate)    Rate
2           1
4           2
3           3```

I want to show the fourth row that is 4 rating zero
How to do this???

Posted 3-Jul-12 3:46am
Mac123341.1K
Updated 3-Jul-12 19:38pm
v3
luisnike19 3-Jul-12 10:08am

I think it's filtering because of your where clause CODE_REVIEWER_ID= 200
akee seth 4-Jul-12 0:57am

I want to know, can we do it in code behind by applying some logic without creating table and using the above query??
MAC123456 4-Jul-12 1:39am

I can't create another table so please give solutions to solve above problem

Rate this:

## Solution 1

Your query is correct, but the result differs from you expecttaion because when the SQL runs it does not know that you are looking for all the ratings from 1 to 4. For that reason, I would create another mapping table like this:

`CREATE TABLE Ratings (Name NVARCHAR(20), Rate INT)`

This table would contain the mappings of Name and Rating ID. Once done, you can use a query like this:

```SELECT
COUNT(CR.Rate) AS [COUNT],
R.rate AS [Rating]
FROM
Code_Review AS CR FULL JOIN Ratings AS R ON
CR.Rate = R.Rate
WHERE
(CR.code_reviewer_id = 200 OR CR.code_reviewer_id IS NULL)
GROUP BY
R.Rate
ORDER BY
R.Rate```

Alternative : OP did not want to have phsyical table. Create a temp table instead.

```CREATE TABLE #Ratings (Name NVARCHAR(20), Rate INT)

INSERT #Ratings	SELECT 'Very Good', 1
INSERT #Ratings	SELECT 'Good', 2
INSERT #Ratings	SELECT 'Very Bad', 4

SELECT
COUNT(CR.Rate) AS [COUNT],
R.rate AS [Rating]
FROM
Code_Review AS CR FULL JOIN #Ratings AS R ON
CR.Rate = R.Rate
WHERE
(CR.code_reviewer_id = 200 OR CR.code_reviewer_id IS NULL)
GROUP BY
R.Rate
ORDER BY
R.Rate

DROP TABLE #Ratings```
v3
MAC123456 4-Jul-12 1:37am

Without creating a table can it solve ?
i can't create another table in my db :(
Manas Bhardwaj 4-Jul-12 3:31am

See my alternative.
Rate this:

## Solution 5

```SELECT coalesce(c.cnt, 0), r.rate
FROM (SELECT 1 AS rate UNION ALL SELECT 2
UNION ALL SELECT 3 UNION ALL SELECT 4) AS r
LEFT JOIN (SELECT COUNT(RATE),RATE
FROM CODE_REVIEW WHERE CODE_REVIEWER_ID= 200
GROUP BY RATE) AS c
ON r.rate = c.rate;```
Rate this:

## Solution 2

Dear Friend,

You want to create a New table as master for the Rating to meet your requirement.

```Select * into #temm from (
Select A='1'
union all
Select A='1'
union all
Select A='2'
union all
Select A='2'
union all
Select A='2'
union all
Select A='2'
union all
Select A='3'
union all
Select A='3'
union all
Select A='3')i

Select * into #Master from (
Select M='1'
union all
Select M='2'
union all
Select M='3'
union all
Select M='4')o

Select M,Vote=Case when Vot=0 then Vot else count(*) end from (
Select M,Vot=isnull(A,'0') from #Master n
left outer join
#temm d on M=A
)i group by M,Vot order by M```
MAC123456 4-Jul-12 3:00am

Without creating a table can it solve ?
i can't create another table in my db :(
Rate this:

## Solution 3

`Table Creation`

```create table rating
(
rate_id		INT,
rname		varchar(30)
)

create table code_Review
(
pk_id			INT,
code_reviewer	INT,
rate			INT
)```

`Insert Values`
```insert into rating values(1,'very good')
insert into rating values(2,'good')

insert into code_review values(1,200,1)
insert into code_review values(2,200,1)
insert into code_review values(3,200,2)
insert into code_review values(4,200,2)
insert into code_review values(5,200,2)
insert into code_review values(6,200,2)
insert into code_review values(7,200,3)
insert into code_review values(8,200,3)
insert into code_review values(9,200,3)```

`Finally your SQL Query`

```select count(cr.pk_id),r.rname
from rating r
left join code_review cr on cr.rate = r.rate_id and cr.code_reviewer = 200
group by r.rname,r.rate_id
order by r.rate_id
```

`Result`
```count       rname
----------- ------------------------------
2           very good
4           good
v2
Rate this:

## Solution 4

You can use the below query if you don't want to create a seperate table for storing the ratings

```SELECT	SUM(CASE WHEN Rate = 1 THEN 1 ELSE 0 END) AS [Count], 1 AS Rate
FROM Code_Review
WHERE code_reviewer_id = 200

UNION ALL

SELECT	SUM(CASE WHEN Rate = 2 THEN 1 ELSE 0 END) AS [Count], 2 AS Rate
FROM Code_Review
WHERE code_reviewer_id = 200

UNION ALL

SELECT	SUM(CASE WHEN Rate = 3 THEN 1 ELSE 0 END) AS [Count], 3 AS Rate
FROM Code_Review
WHERE code_reviewer_id = 200

UNION ALL

SELECT	SUM(CASE WHEN Rate = 4 THEN 1 ELSE 0 END) AS [Count], 4 AS Rate
FROM Code_Review
WHERE code_reviewer_id = 200
ORDER BY Rate```

Top Experts
Last 24hrsThis month
 OriginalGriff 490 CPallini 135 Maciej Los 130 F-ES Sitecore 105 AdvancedDNA 85
 OriginalGriff 6,239 CHill60 3,490 Maciej Los 3,083 Jochen Arndt 1,975 ppolymorphe 1,880