Just another general SQL question regarding grouping. Basically, I'm pulling 25 rows from a table, and would like to group rows by a particular field (we'll call it field1). I'd do something like this:

SELECT *, COUNT(row_id) AS total FROM table GROUP BY field1 LIMIT 25

This nicely clumps together the rows that share the same value in field1, leaving the total variable to signify how many rows have been grouped. However, what I'm looking to do is as follows:

  field1   |   field2 
-----------------------
   corn    |   pizza
   hotdog  |   cow
   corn    |   man

The above query will take this result set and will group row 1 and 3 because they both share an identical value for field1. It will also return row 2. What's the best way to also grab each of the grouped rows other values? So effectively, if more than one row is found with an identical field1 value, I'd like to be able to say:

Two rows had a value of corn in field1. These two rows had the following values for field2: pizza, man.

What's the best way to handle this?

An embedded SQL select query when doing the WHERE clause.

I have absolutely no concept of that. Any good online resources?

Perhaps:

SELECT field1, GROUP_CONCAT(field2), COUNT(*) FROM foo GROUP BY field1

Or two queries if you need the individual values...