Find and merge MySQL rows within PHP?

[mslilafowler]

Hi

I have a MySQL table which has multiple similar rows, like this:

+----+------+------+-------+
| ID | User | Rows | Price |
+----+------+------+-------+
|  1 | John |    4 |     1 |
|  2 | John |    4 |     1 |
|  3 | John |    4 |     1 |
|  4 | Lisa |    7 |     3 |
|  5 | Lisa |    7 |     3 |
|  6 | Lisa |    6 |     3 |
|  7 | John |    5 |     1 |
|  8 | John |    5 |     1 |
+----+------+------+-------+

The end result I want is to see this:

“John has 3 rows at $1 each”
“Lisa has 2 rows at $3 each”
“Lisa has 1 row at $3 each”
“John has 2 rows at $1 each”

I’ve thought of a few different approaches like using COUNT, but I can’t get to the proper algorithm to get to this result. What do you suggest?

[phdr]

Based on the posted information, you would use COUNT(*) to give the count of rows within each group, and you would use GROUP BY Rows to produce the groups.

[mslilafowler]

I tried this:

SELECT COUNT(*) FROM mytable GROUP BY user, rows, price

So far it’s giving the result I need, thank you for your suggestion there’s something that’s missing though - let me come back after a few test runs

(Edit - tested and works wonderfully!)