Page 1 of 1
add numbers in a table field
Posted: Thu Feb 09, 2006 6:29 pm
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
Posted: Thu Feb 09, 2006 6:32 pm
by josh
Also a more efficient count be acheived with
Posted: Thu Feb 09, 2006 6:37 pm
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
in the above code?
Posted: Thu Feb 09, 2006 6:40 pm
by josh
Code: Select all
$res = mysql_query("select SUM(`field`) from users");
$riders = mysql_result($res,0,0);
Posted: Fri Feb 10, 2006 5:00 am
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
Re: add numbers in a table field
Posted: Fri Feb 10, 2006 5:23 am
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'];
}
Re: add numbers in a table field
Posted: Fri Feb 10, 2006 5:36 am
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....
