Click here to Skip to main content
12,358,777 members (61,294 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: ASP.NET SQL MySQL
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.
3-bad.
4-very bad.

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

Thanks in advance
Posted 3-Jul-12 3:46am
Mac123341.1K
Updated 3-Jul-12 19:38pm
v3
Comments
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: bad
 
good
Please Sign up or sign in to vote.

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 'Bad', 3
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
  Permalink  
v3
Comments
MAC123456 4-Jul-12 1:37am
   
Without creating a table can it solve ?
i can't create another table in my db :(
other solutions please..
Manas Bhardwaj 4-Jul-12 3:31am
   
See my alternative.
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

This query can help you:
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;
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

Dear Friend,

You want to create a New table as master for the Rating to meet your requirement.
I have given you a small eg,. Hope it help you.

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
  Permalink  
Comments
MAC123456 4-Jul-12 3:00am
   
Without creating a table can it solve ?
i can't create another table in my db :(
other solutions please..
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

Your Solution:

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 rating values(3,'bad')
insert into rating values(4,'very bad')
 

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
3           bad
0           very bad
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

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
  Permalink  

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

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160621.1 | Last Updated 4 Jul 2012
Copyright © CodeProject, 1999-2016
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100