I need to build an sql Query using select retrieving object

Question

i am having trouble in writing SQL query

I have a database mysql CRICKET:

id  cricketer run cricketer2 run2 cricketer3 run3
9    dhoni    100   ashwin    50    raina     25
10    ABD     100   gayle     99    virat     15

Using the above database i have to take cricketers and add n=3;

$sql = "select (cricketer+cricketer2+cricketer3) as crick, 
               (run+run2+run3/n) as run 
        from CRICKET 
        WHERE id ='9' "
mysql_query($sql) or die(mysql_error());

I need the data look like this after using select query

crick  run
dhoni  58
ashwin 58
raina  58*

can anyone adjust the query my above query is not working

can you tell me, run showing in the field is avg. value ? and You may need to display like this ? crick run dhoni 58 ashiwn 58 raina 58 ABD 71.33 gayle 71.33 virat 71.33 Am I right ? — Miqdad Ali
– Miqdad Ali, Commented Jan 28, 2014 at 6:42
@sleepy , you should explain what you are trying to achieve. Not just the final outcome — Don Srinath
– Don Srinath, Commented Jan 28, 2014 at 6:53
oh sorry i am doing one project and i cant reveal the secret so i will post only the snippet @srinath — sleepy
– sleepy, Commented Jan 28, 2014 at 7:42
@miqdad all the run must add i.e run+run2+run3 and divide by 3 dynamically it must get the avg value — sleepy
– sleepy, Commented Jan 28, 2014 at 7:43

spaceman · Accepted Answer · 2014-01-28 06:54:48Z

2

SELECT `cricheter`, `cricketer2`, `cricketer3`, 
         ROUND ( (`run` + `run2` + `run3`)/3 ) AS result
                FROM `run` WHERE `id` = 9;

This will return:

 cricheter   cricketer2   cricketer3   result
   dhoni       ashwin       raina        58

If that's what you want...

P.S. The structure of the table can and should probably be improved.

edited Jan 28, 2014 at 6:54

answered Jan 28, 2014 at 6:43

spaceman

8339 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Miqdad Ali · Accepted Answer · 2014-01-28 06:47:27Z

0

Here is the code

<?php
    $sql = "SELECT
            *
        FROM
            CRICKET
        WHERE
            id = 9
    ";
    $result = mysql_query($sql) or die(mysql_error());
    $row = mysql_fetch_array($resul,MYSQL_ASSOC);
    $avg_run = ($row['run'] + $row['run2'] + $row['run3'])/3
?>
<table>
    <tr>
        <td>Crick</td>
        <td>Run</td>
    </tr>
    <tr>
        <td><?php echo $row['cricketer']; ?></td>
        <td><?php echo $avg_run; ?></td>
    </tr>
    <tr>
        <td><?php echo $row['cricketer2']; ?></td>
        <td><?php echo $avg_run; ?></td>
    </tr>
    <tr>
        <td><?php echo $row['cricketer3']; ?></td>
        <td><?php echo $avg_run; ?></td>
    </tr>
</table>

answered Jan 28, 2014 at 6:47

Miqdad Ali

6,1577 gold badges34 silver badges50 bronze badges

Collectives™ on Stack Overflow

I need to build an sql Query using select retrieving object

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related