Combing Rows

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
davemh
Forum Newbie
Posts: 3
Joined: Wed May 10, 2006 8:17 pm

Combing Rows

Post 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
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post by Christopher »

SELECT DISTINCT name, GROUP_CONCAT( stat1, stat2) FROM DAVE GROUP BY name
(#10850)
davemh
Forum Newbie
Posts: 3
Joined: Wed May 10, 2006 8:17 pm

Post 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.
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
User avatar
dibyendrah
Forum Contributor
Posts: 491
Joined: Wed Oct 19, 2005 5:14 am
Location: Nepal
Contact:

Post 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
davemh
Forum Newbie
Posts: 3
Joined: Wed May 10, 2006 8:17 pm

Post 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.
Post Reply