12,398,000 members (43,352 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 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.

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

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

This question can be useful to other users

Rate this:

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

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

Rate this:

Rate this:

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

Top Experts
Last 24hrsThis month
 OriginalGriff 380 Richard Deeming 244 Richard MacCutchan 200 Karthik Bangalore 190 ppolymorphe 110
 OriginalGriff 6,508 Karthik Bangalore 2,572 ppolymorphe 2,540 F-ES Sitecore 1,977 Richard MacCutchan 1,952