Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: C#1.0 C#2.0 C#3.0 C# ASP.NET SQL , +
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
Edited 3-Jul-12 21:10pm
v4
Comments
MAC123456 at 4-Jul-12 3:07am
   
If anyone unable to understand my question than i can give more examples....
Please Help
Thanks
A.J Bosch at 4-Jul-12 4:25am
   
This question can be useful to other users
Rate this: bad
good
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>
  Permalink  
Comments
MAC123456 at 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 at 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 at 6-Jul-12 2:37am
   
Thanks for reply :)
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

  Permalink  
Rate this: bad
good
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 Smile | :)
  Permalink  
v2
Comments
MAC123456 at 4-Jul-12 4:48am
   
No no i want all the 4 rows.
A.J Bosch at 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 at 4-Jul-12 5:14am
   
no
got the solution
A.J Bosch at 4-Jul-12 5:21am
   
shot no probz then
enjoy
bhagirathimfs at 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)

  Print Answers RSS
0 OriginalGriff 370
1 Sergey Alexandrovich Kryukov 195
2 Prakriti Goyal 177
3 jlopez788 134
4 _Amy 110
0 OriginalGriff 6,762
1 Sergey Alexandrovich Kryukov 5,539
2 Maciej Los 3,479
3 Peter Leow 3,323
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web01 | 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