SQL Syntax for adding multiple rows together from 1 column

jfergy · #1 (**permalink**) Jan 17th, 2006, 00:28

Hello-

I want to add the column office_cost for all rows that are owned by a particular company. How can i do that? The column is a decimal 5,2 and needs to total all rows for that company so i can output a total cost.

grahame · #2 (**permalink**) Jan 17th, 2006, 02:24

You should use a sum function on the column that you want to total up, and a group by clause to combine each bundle of records. Here's an example of houses for sale and the seller which totals up the value of properties for each selling agent.

Code: Select all

mysql> select agid, asking from sales;
+------+--------+
| agid | asking |
+------+--------+
|   10 | 225000 |
|   10 | 195000 |
|   10 | 237500 |
|    8 | 152000 |
|    8 | 205000 |
|    6 | 229950 |
|    5 | 335000 |
|    1 |  96950 |
|    5 | 279950 |
|    4 | 149950 |
|    1 | 220000 |
|    4 | 127500 |
|   11 | 465000 |
|    3 | 222000 |
|    3 | 465000 |
|    2 | 140000 |
|    2 | 116000 |
|   11 | 275000 |
|    4 | 250000 |
+------+--------+
19 rows in set (0.00 sec)

mysql> select agid, sum(asking) from sales group by agid;
+------+-------------+
| agid | sum(asking) |
+------+-------------+
|    1 |      316950 |
|    2 |      256000 |
|    3 |      687000 |
|    4 |      527450 |
|    5 |      614950 |
|    6 |      229950 |
|    8 |      357000 |
|   10 |      657500 |
|   11 |      740000 |
+------+-------------+
9 rows in set (0.40 sec)

mysql>

jfergy · #3 (**permalink**) Jan 17th, 2006, 03:54

Thanks, I got it!

guelphdad · #4 (**permalink**) Jan 17th, 2006, 04:12

Code: Select all

sum(columname) group by columname

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Adding 1 to Column Value	Jack Franklin	PHP Forum	4	Feb 3rd, 2008 10:19
Adding a column to a table	Jack Franklin	Databases	2	Feb 3rd, 2008 08:17
Adding Content to Multiple Pages	longinwa	Starting Out	5	Jun 19th, 2007 00:26
multiple rows => 1row???	ktsirig	PHP Forum	1	Dec 31st, 2005 21:30
..copy data from column A in Table A to column B in Table B?	gecastill	Databases	10	Jun 23rd, 2005 18:27

		Webforumz.com > Main Forums > Program Your Website > Databases
SQL Syntax for adding multiple rows together from 1 column