Page 1 of 1

Combing Rows

Posted: Wed May 10, 2006 8:25 pm
by davemh
I have searched through several pages and have not found a solution to my problem:

For simplicity's sake, I have a table with 4 fields:

Code: Select all

id     name        stat1     stat2
     --     --------    -------    -------
      1     Dave         23        0     
      2     Larry        12        0
      3     Dave          0        4
      4     Larry         0        18
What I want is if the name id duplicated, combine the rows, so:

Code: Select all

id     name        stat1     stat2
     --     --------    -------    -------
      1     Dave         23        4     
      2     Larry        12        18
would be the result.

I have been trying to use:
'SELECT name, GROUP_CONCAT( stat1, stat2) FROM DAVE GROUP BY name'

But have not had any luck. Anyone have some insight on how I can accomplish this?

Any help is greatly appreciated!

Dave

Posted: Wed May 10, 2006 8:27 pm
by Christopher
SELECT DISTINCT name, GROUP_CONCAT( stat1, stat2) FROM DAVE GROUP BY name

Posted: Wed May 10, 2006 8:48 pm
by davemh
Ok, perhaps I am just missing something here

Code: Select all

$connect = mysql_connect($host, $user, $pswd)
  or die("Could not connect: " . mysql_error());
$database = mysql_select_db("$dbname")
  or die(MySQL_Error());

$sql = "SELECT DISTINCT name, GROUP_CONCAT( stat1, stat2) FROM DAVE GROUP BY name";

mysql_query($sql) or die(mysql_error());

mysql_close();

?>
Does the SELECT statement execution accomplish the row condensing by itself? Or is there more I must do? The code above has not altered the table.

This is on a MySQL/PHP 5 server if that makes any difference.

Thanks again for assisting with this.

Posted: Thu May 11, 2006 2:43 am
by dibyendrah
The result just came out like this :

Code: Select all

mysql> insert into test4 values(1,'Dave', 23, 0); Query OK, 1 row affected (0.01 sec)

mysql> insert into test4 values(2,'Larry', 12, 0); Query OK, 1 row affected (0.00 sec)

mysql> insert into test4 values(3,'Dave', 0, 4); Query OK, 1 row affected (0.00 sec)

mysql> insert into test4 values(4,'Larry', 0, 18);
Query OK, 1 row affected (0.00 sec)

mysql> SELECT DISTINCT name, GROUP_CONCAT( stat1, stat2) FROM test4 GROUP BY name;
+-------+-----------------------------+
| name  | GROUP_CONCAT( stat1, stat2) |
+-------+-----------------------------+
| Dave  | 230,04                      |
| Larry | 120,018                     |
+-------+-----------------------------+
2 rows in set (0.00 sec)

davemh, I think you need to sum instead of cancatenation. Right ?


Cheers,
Dibyendra

Posted: Thu May 11, 2006 2:49 am
by dibyendrah
But I'm afraid that can we sum the two colum and group it up according to the name ? Confused :?

Any solution ?

Dibyendra

Posted: Thu May 11, 2006 3:08 pm
by davemh
dibyendrah wrote:But I'm afraid that can we sum the two colum and group it up according to the name ? Confused :?

Any solution ?

Dibyendra
You are absolutely correct, summing the columns would be a better solution.

Following your advice I executed the query from the command line and see where the tables are being altered, but when I then execute

Code: Select all

SELECT * from DAVE;
the table looks like it did originally. I know realize I must rewrite the table once I have executed the summing query.

Thankyou for your help.