Click here to Skip to main content
Rate this: bad
good
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
Edited 3-Jul-12 19:38pm
v3
Comments
luisnike19 at 3-Jul-12 10:08am
   
I think it's filtering because of your where clause CODE_REVIEWER_ID= 200
akee seth at 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 at 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 at 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 at 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 at 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
0 Nirav Prabtani 268
1 OriginalGriff 217
2 Mika Wendelius 185
3 _Amy 170
4 CPallini 170
0 Nirav Prabtani 284
1 OriginalGriff 217
2 Mika Wendelius 185
3 _Amy 170
4 Sergey Alexandrovich Kryukov 154


Advertise | Privacy | Mobile
Web04 | 2.8.140721.1 | Last Updated 4 Jul 2012
Copyright © CodeProject, 1999-2014
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