SQL help needed

Trcyng

25 Dec 08, 21:31

Hi guys.

I would need someone to help me with my database query.

<?php

    $query ="SELECT COUNT(team_id) FROM allocated ORDER BY team_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    $data.="Total Number of Sales Team is ".$row['COUNT(team_id)']." ";
    }

    $query = "SELECT COUNT(staff_id) FROM staff ORDER BY staff_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    $data.="Total Number of Sales Staff is ".$row['COUNT(staff_id)']." ";
    }

?>

Is there anyway that I could have like average Sales_Staff to Sales_Team...?

Like say the 1st query the value I got is 100 Teams

2nd query is 300 Sales Staff.

Is there anyway that I can make it to 3 staff per team?

Is it possible that I could merge the 2 query together and do a logical divisioN?

please help and thanks!

caleb_chiang

25 Dec 08, 21:41

Erm... the simplest way is to rename your 2nd query with a 2 behind.

Then echo out the result of 2nd query / 1st query lor.

Trcyng

25 Dec 08, 21:42

any examples like the ones above...?

thanks!

caleb_chiang

25 Dec 08, 21:56

I would do it this way though...

$query ="SELECT * FROM allocated ORDER BY team_id";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_num_rows($result);

$data.="Total Number of Sales Team is ".$row." ";

$query2 = "SELECT * FROM staff ORDER BY staff_id";
$result2 = mysql_query($query2) or die(mysql_error());
$row2 = mysql_num_rows($result2);
$data.="Total Number of Sales Staff is ".$row2." ";

$row3 = $row2/$row;

$data.="Average sales staffs per team is ".$row3." ";

echo $data;

Trcyng

26 Dec 08, 14:41

Then I have got another problem.

<?php

    $query ="SELECT SUM(income) FROM customer ORDER BY cust_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    echo $row['SUM(income)'];
    }

    ?>

This statement calculate the number of rows in my databse. Is there a way that I could add to the total Values from the income colomn?

The DB table is customer and the colomn is income. Is there a way to add all the values in the colomn of Income?

Trcyng

26 Dec 08, 15:56

1           2
----------- -----------
          1           1
          1           2
          2           1
          2           2


Like the ones above. How could I add the values from Table 2. Like 1+2+1+2

In the context of 

<?php
                        Â Â  

$query ="SELECT SUM(income) FROM customer ORDER BY cust_id ASC";

$result = mysql_query($query) or die(mysql_error());

                        
while($row = mysql_fetch_array($result)){
echo $row['SUM(income)'];
}
 ?>

Trcyng

27 Dec 08, 15:59

could anyone help me please?

RETARDED_MORON

27 Dec 08, 16:06

Originally posted by Trcyng:

Then I have got another problem.

<?php

    $query ="SELECT SUM(income) FROM customer ORDER BY cust_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    echo $row['SUM(income)'];
    }

    ?>

This statement calculate the number of rows in my databse. Is there a way that I could add to the total Values from the income colomn?

The DB table is customer and the colomn is income. Is there a way to add all the values in the colomn of Income?

I'm not sure if that would calculate the number of rows, but this would:

$query = "SELECT income FROM customer WHERE <condition, if necessary>";
$result = mysql_query($query);
$totalrows = mysql_num_rows($result);

echo "Number of rows: ".$totalrows;

To add all the values in the income column, the SQL query should be:

"SELECT SUM(income) AS 'totalIncome' FROM customer WHERE <condition, if necessary"

teraexa

27 Dec 08, 16:12

Originally posted by RETARDED_MORON:

I'm not sure if that would calculate the number of rows, but this would:

$query = "SELECT income FROM customer WHERE <condition, if necessary>";
$result = mysql_query($query);
$totalrows = mysql_num_rows($result);

echo "Number of rows: ".$totalrows;

To add all the values in the income column, the SQL query should be:

"SELECT SUM(income) AS 'totalIncome' FROM customer WHERE <condition, if necessary"

RETARDED_MORON is correct.

You can try this also:

$query = "SELECT income FROM customer WHERE <condition, if necessary>";
$result = mysql_query($query);

$totalrows = mysql_num_rows($result);

$sum=0;

while($result_array=mysql_fetch_array($result))

$sum+=$result_array['income'];

RETARDED_MORON

27 Dec 08, 17:39

Originally posted by teraexa:

RETARDED_MORON is correct.

You can try this also:

$query = "SELECT income FROM customer WHERE <condition, if necessary>";
$result = mysql_query($query);

$totalrows = mysql_num_rows($result);

$sum=0;

while($result_array=mysql_fetch_array($result))

$sum+=$result_array['income'];

Your username seems familiar. Were you from RJC?

teraexa

27 Dec 08, 17:52

Originally posted by RETARDED_MORON:

Your username seems familiar. Were you from RJC?

Yep.

LatecomerX

28 Dec 08, 05:49

Originally posted by Trcyng:

Hi guys.

I would need someone to help me with my database query.

<?php

    $query ="SELECT COUNT(team_id) FROM allocated ORDER BY team_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    $data.="Total Number of Sales Team is ".$row['COUNT(team_id)']." ";
    }

    $query = "SELECT COUNT(staff_id) FROM staff ORDER BY staff_id ASC";
    $result = mysql_query($query) or die(mysql_error());

    while($row = mysql_fetch_array($result)){
    $data.="Total Number of Sales Staff is ".$row['COUNT(staff_id)']." ";
    }

?>

Is there anyway that I could have like average Sales_Staff to Sales_Team...?

Like say the 1st query the value I got is 100 Teams

2nd query is 300 Sales Staff.

Is there anyway that I can make it to 3 staff per team?

Is it possible that I could merge the 2 query together and do a logical divisioN?

please help and thanks!

Avoid using while() loops and ORDER BY clauses unnecessarily as this would cause your application to take up additional resources which could have been avoided.

$query = 'SELECT
  (SELECT COUNT(team_id) FROM allocated) AS team,
  (SELECT COUNT(staff_id) FROM staff) AS staff,
  (team / staff) AS ratio';

($result = mysql_query($query)) || exit(mysql_error());

list($team, $staff, $ratio) = mysql_fetch_row($result); // $ratio now contains the result of dividing the number of staff by the number of teams

Sources:
http://forums.devarticles.com/general-sql-development-47/count-from-multiple-tables-14903.html
http://dev.mysql.com/doc/refman/5.0/en/arithmetic-functions.html

Trcyng

28 Dec 08, 19:13

I have a last problem. It is regarding the ROWS vs. VALUES.

Thanks for the help guys and I regretted not taking the programming as my specialisation.

See this codes:

<?php

    $query = "SELECT income FROM customer";
    $result = mysql_query($query);
    $totalrows = mysql_num_rows($result);
    $sum=0;
    while($result_array=mysql_fetch_array($result))
    $sum+=$result_array['income'];

    // This was taken from the post above and the VALUE IS < $377413 >

    $query2 = "SELECT SUM(cust_id) FROM customer ORDER BY cust_id ASC";
    $result2 = mysql_query($query2) or die(mysql_error());
    $row2 = mysql_num_rows($result2);

// This was done and the RESULT IS <88>

// Now I want to do is an average Income/customer and the logical answer is $4288
// So logically this is done like that isn't it?

$row3 = FLOOR($sum/$row2);

echo $row3;

// But how come it the result is $0...? I suspect that this is the rows issues...
?>

Any help guys?

Trcyng

28 Dec 08, 22:06

anyone please help me...?

Trcyng

28 Dec 08, 22:06

anyone please help me...?

teraexa

28 Dec 08, 22:30

Originally posted by Trcyng:

I have a last problem. It is regarding the ROWS vs. VALUES.

Thanks for the help guys and I regretted not taking the programming as my specialisation.

See this codes:

<?php

    $query = "SELECT income FROM customer";
    $result = mysql_query($query);
    $totalrows = mysql_num_rows($result);
    $sum=0;
    while($result_array=mysql_fetch_array($result))
    $sum+=$result_array['income'];

    // This was taken from the post above and the VALUE IS < $377413 >

    $query2 = "SELECT SUM(cust_id) FROM customer ORDER BY cust_id ASC";
    $result2 = mysql_query($query2) or die(mysql_error());
    $row2 = mysql_num_rows($result2);

    // This was done and the RESULT IS <88>

// Now I want to do is an average Income/customer and the logical answer is $4288
// So logically this is done like that isn't it?

    $row3 = FLOOR($sum/$row2);

    echo $row3;

// But how come it the result is $0...? I suspect that this is the rows issues...
    ?>

Any help guys?

can try

    $query2 = "SELECT SUM(cust_id) FROM customer ORDER BY cust_id ASC";
    $result2 = mysql_query($query2) or die(mysql_error());
    $row2 = (int) mysql_num_rows($result2);

and

$query = "SELECT income FROM customer";
    $result = mysql_query($query);
    $totalrows = mysql_num_rows($result);
    $sum=0;
    while($result_array=mysql_fetch_array($result))
    $sum+=(int) $result_array['income'];

and see what's the result? I am afraid typecasting may be the problem here.

In any case, maybe you would like to try this instead:

    $query = "SELECT FLOOR(AVG(income)) average FROM customer ORDER BY cust_id ASC";
    $result = mysql_query($query) or die(mysql_error());
    $average = (int) mysql_fetch_array['average'];

echo $average;

Trcyng

28 Dec 08, 22:45

The 1st and the second is the same as the codes that I had.

the trial part seemed... "Parse error: syntax error, unexpected '['" I tried changing but it is not valid.

teraexa

28 Dec 08, 22:49

Originally posted by Trcyng:

The 1st and the second is the same as the codes that I had.

the trial part seemed... "Parse error: syntax error, unexpected '['" I tried changing but it is not valid.

hmm. sorry my fault. didn't note about the wrong use of mysql_fetch_array/mysql_fetch_row, try:

$query = "SELECT FLOOR(AVG(income)) FROM customer ORDER BY cust_id ASC";
$result = mysql_query($query) or die(mysql_error());
$query_result = mysql_fetch_row($result);

echo $query_result[0];

Trcyng

28 Dec 08, 23:19

it still states to be syntax error. Oh yes, the database that I am using is mySQL server

teraexa

28 Dec 08, 23:34

Originally posted by Trcyng:

it still states to be syntax error. Oh yes, the database that I am using is mySQL server

hmm. sorry my fault. didn't note about the wrong use of mysql_fetch_array/mysql_fetch_row, try:

$query = "SELECT FLOOR(AVG(income)) FROM customer ORDER BY cust_id ASC";
$result = mysql_query($query) or die(mysql_error());
$query_result = mysql_fetch_row($result);

echo $query_result[0];

Trcyng

28 Dec 08, 23:41

It works!!! Thank you very much!!! Are you a programmer...?

=)

teraexa

28 Dec 08, 23:44

Originally posted by Trcyng:

It works!!! Thank you very much!!! Are you a programmer...?

=)

nope. just used to program back in secondary school.

these two should be your good friends in PHP/MySQL:

http://sg2.php.net/manual/en/langref.php

http://dev.mysql.com/doc/refman/5.1/en/sql-syntax.html