need help in sql query

Question

5.00/5 (1 vote)

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

SQL

Count(Rate)    Rate
    2           1
    4           2
    3           3
    0           4

I have tried

SQL

SELECT COUNT(RATE),RATE FROM CODE_REVIEW WHERE CODE_REVIEWER_ID= 200 GROUP BY RATE;

It is showing result

SQL

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

Mac12334

Updated 3-Jul-12 19:38pm

v3

Add a Solution

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

Mac12334 4-Jul-12 1:39am

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

5 solutions

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.

SQL

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

Posted 3-Jul-12 4:23am

Arunprasath Natarajan

Comments

Mac12334 4-Jul-12 3:00am

Without creating a table can it solve ?
i can't create another table in my db :(
other solutions please..

Solution 3

Your Solution:

Table Creation

SQL

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

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

Insert Values

SQL

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

SQL

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

Posted 3-Jul-12 21:52pm

MaulikDusara

Updated 3-Jul-12 21:55pm

v2

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

Posted 3-Jul-12 21:59pm

__TR__

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Manas Bhardwaj · Accepted Answer · 2012-07-03T04:12:00

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:

SQL

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

akee seth · Accepted Answer · 2012-07-03T23:08:00

This query can help you:

SQL

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;