Click here to Skip to main content
12,398,000 members (43,352 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C#2.0 C#3.0 C# 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 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
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 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: 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 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)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web02 | 2.8.160721.1 | Last Updated 4 Jul 2012
Copyright © CodeProject, 1999-2016
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