13,193,442 members (40,932 online)
Rate this:
Please Sign up or sign in to 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
```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 in the gridview
Please Give me some logic to do this

I have already put this question but all the experts are saying to do another table but in my case i can't create another table.Without Creating another table how to solve that problem.

So please help me to find the how to show 4 rows using query or give some logic so that i can show 4 rows i the GridView.

Sorry for re-post the question...

Actually i didn't find the proper answer.

Thanks in advance
Posted 3-Jul-12 21:06pm
Mac123341.1K
Updated 3-Jul-12 21:10pm
v4
Comments
MAC123456 4-Jul-12 3:07am

If anyone unable to understand my question than i can give more examples....
Please Help
Thanks
A.J Bosch 4-Jul-12 4:25am

This question can be useful to other users

## 3 solutions

Rate this:
Please Sign up or sign in to vote.

## Solution 2

```Your Solution:

<code>Table Creation</code>

<pre lang="SQL">create table rating
(
rate_id     INT,
rname       varchar(30)
)

create table code_Review
(
pk_id           INT,
code_reviewer   INT,
rate            INT
)
</pre>

<code>Insert Values</code>
<pre lang="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)</pre>

<code>Finally your SQL Query</code>

<pre lang="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

</pre>

<code>Result</code>
<pre>count       rname
----------- ------------------------------
2           very good
4           good
3           bad
0           very bad</pre>```
Comments
MAC123456 4-Jul-12 4:50am

I already mentioned that i can't create another table
if it can't solve by sql query give some ASP.NET C# login to solve the problem.
MaulikDusara 5-Jul-12 0:33am

Table creation is only for an example for your tables 1st one is rate table and 2nd one is your data table from which you need to access data. You no need to create another table for this.
bhagirathimfs 6-Jul-12 2:37am

Thanks for reply :)
Rate this:
Please Sign up or sign in to vote.

## Solution 4

Rate this:
Please Sign up or sign in to vote.

## Solution 1

Try this

`SELECT ISNULL (FIELDNAME,0), SOME_OTHER_FIELD FROM TABLE`

If I'm not mistaken this is what your looking 4

`SELECT ISNULL (COUNT(RATE),0),RATE FROM CODE_REVIEW WHERE CODE_REVIEWER_ID= 200 GROUP BY RATE`

Try and let me know if it works :)
v2
Comments
MAC123456 4-Jul-12 4:48am

No no i want all the 4 rows.
A.J Bosch 4-Jul-12 5:06am

so you only have 1 table witch for user 200 only has rate's 1,2and 3 there is no number 4?

do you have another table that specifies that there are 4 ratings?
eg.
1-very good.
2-good.
3-bad.
4-very bad.
where does the system get the ratings from?
bhagirathimfs 4-Jul-12 5:14am

no
got the solution
A.J Bosch 4-Jul-12 5:21am

shot no probz then
enjoy
bhagirathimfs 6-Jul-12 2:36am

Thanks for reply :)

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

Top Experts
Last 24hrsThis month
 OriginalGriff 385 Jochen Arndt 255 Karthik Bangalore 200 Kornfeld Eliyahu Peter 160 CPallini 130
 OriginalGriff 6,550 ppolymorphe 1,639 Karthik Bangalore 1,354 Jochen Arndt 1,020 Richard Deeming 824

Advertise | Privacy |
Web01 | 2.8.171018.2 | Last Updated 4 Jul 2012
Copyright © CodeProject, 1999-2017
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