add numbers in a table field

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

add numbers in a table field

Post by jasondavis »

Code: Select all

$res = mysql_query("select * from users order by intPriority desc");
$riders = mysql_num_rows($res);
I have this code above which gives me the row count

can someone give me the full code to get from the same table I have a field named intMSFriends which always has a value of a number in it, I would like to all up all the numbers in this field to give me a total
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Code: Select all

SELECT SUM(`myField`)
Also a more efficient count be acheived with

Code: Select all

SELECT COUNT (*)
jasondavis
Forum Commoner
Posts: 60
Joined: Sat Feb 04, 2006 5:35 pm

Post by jasondavis »

Code: Select all

$res = mysql_query("select * from users order by intPriority desc"); 
$riders = mysql_num_rows($res);
sorry im really new, so where would I put

Code: Select all

SELECT SUM(`intMSFriends`)
in the above code?
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Post by josh »

Code: Select all

$res = mysql_query("select SUM(`field`) from users"); 
$riders = mysql_result($res,0,0);
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post by raghavan20 »

a few examples to make things clearer...

Code: Select all

mysql> describe test4;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| somenumber | tinyint(4)  | YES  |     | NULL    |       |
| somename   | varchar(30) |      |     |         |       |
+------------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

mysql> select * from test4;
+------------+----------+
| somenumber | somename |
+------------+----------+
|          3 | gg       |
|          5 | yh       |
|         23 | gg       |
|        127 | yh       |
|        127 | msft     |
+------------+----------+
5 rows in set (0.00 sec)

mysql> select sum(somenumber) from test4;
+-----------------+
| sum(somenumber) |
+-----------------+
|             285 |
+-----------------+
1 row in set (0.01 sec)

mysql> select sum(somenumber), somename from test4
    -> group by somename;
+-----------------+----------+
| sum(somenumber) | somename |
+-----------------+----------+
|              26 | gg       |
|             127 | msft     |
|             132 | yh       |
+-----------------+----------+
3 rows in set (0.00 sec)

some exceptions...weird statements you can say that...i am just showing you these to enable better understanding.
/* there are actually five rows available but four rows are displayed because two rows have the same value 127...I am performing sum on a varchar field so it returns zero for each row...*/
mysql> select sum(somename) from test4
    -> group by somenumber;
+---------------+
| sum(somename) |
+---------------+
|             0 |
|             0 |
|             0 |
|             0 |
+---------------+
4 rows in set (0.00 sec)

mysql> select sum(somenumber) from test4
    -> group by somenumber;
+-----------------+
| sum(somenumber) |
+-----------------+
|               3 |
|               5 |
|              23 |
|             254 | /*addition of two 127s */
+-----------------+
4 rows in set (0.00 sec)

/* this returns error because 'sum' uses grouping and returns only one value but somename would yield all values in the table so both of them cannot go with each other...you can only use one of them at any time.... */
mysql> select sum(somenumber), somename from test4;
ERROR 1140 (42000): Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GR
OUP columns is illegal if there is no GROUP BY clause
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: add numbers in a table field

Post by Benjamin »

jasondavis wrote:

Code: Select all

$res = mysql_query("select * from users order by intPriority desc");
$riders = mysql_num_rows($res);
I have this code above which gives me the row count

can someone give me the full code to get from the same table I have a field named intMSFriends which always has a value of a number in it, I would like to all up all the numbers in this field to give me a total

Code: Select all

$Total = null;
$Result = mysql_query("select `OnlyTheFieldsYouNeed`, `intMSFriends` from `users` order by `intPriority` desc");

while ($Data = mysql_fetch_assoc($Result)) {
  $Total = $Total + $Data['intMsFriends'];
}
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Re: add numbers in a table field

Post by raghavan20 »

agtlewis wrote:
jasondavis wrote:

Code: Select all

$res = mysql_query("select * from users order by intPriority desc");
$riders = mysql_num_rows($res);
I have this code above which gives me the row count

can someone give me the full code to get from the same table I have a field named intMSFriends which always has a value of a number in it, I would like to all up all the numbers in this field to give me a total

Code: Select all

$Total = null;
$Result = mysql_query("select `OnlyTheFieldsYouNeed`, `intMSFriends` from `users` order by `intPriority` desc");

while ($Data = mysql_fetch_assoc($Result)) {
  $Total = $Total + $Data['intMsFriends'];
}
it is preferrable to do most of the operations in the Mysql server because it is faster.... :)
Post Reply