Need help in Logic to Show in GridView

Question

4.33/5 (3 votes)

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

Mac12334

Updated 3-Jul-12 21:10pm

v4

Add a Solution

Comments

Mac12334 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

Solution 2

XML

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>

Posted 3-Jul-12 22:01pm

MaulikDusara

Comments

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

[no name] 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 :)

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

Posted 3-Jul-12 21:50pm

A.J Bosch

Updated 3-Jul-12 21:53pm

v2

Comments

Mac12334 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

nogot the solution

A.J Bosch 4-Jul-12 5:21am

shot no probz then
enjoy

bhagirathimfs 6-Jul-12 2:36am

Thanks for reply :)

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)

bhagirathimfs · Accepted Answer · 2012-07-03T23:12:00

Solution 4

Solved need help in sql query[^]

Posted 3-Jul-12 23:12pm

bhagirathimfs