How to group rows in a single sql query?

Question

easy question i think.

I have this table

+-----------------------------+
+ ip | campaignid             +
+-----------------------------+
+  1.1.1.1 | 3                +
+  1.1.1.1 | 17               +
+  1.1.1.1 | 4                +                 
+  2.2.2.2 | 8                +
+-----------------------------+

i want to get a query where ip = '1.1.1.1' like this:

+-----------------------------+
+ ip       | count | campaigns+
+-----------------------------+
+  1.1.1.1 |     3 | 3,17,4   +
+-----------------------------+

I know how to get this done in php, but is it possible to get this result with an sql query?

Thank you very much in advance.

Have a look at this: http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql — Paweł Duda, Mar 23 '14 at 23:49
@Strawberry look at Jared Farrish's answer... do you think it is more efficient to do this in php than sql? thanks — Lucas, Mar 23 '14 at 23:55

score 4 · Accepted Answer · answered Mar 23 '14 at 23:48

4

SELECT
   ip
 , COUNT(ip) AS count
 , GROUP_CONCAT(compaignid) AS campaigns
FROM table_name
GROUP BY ip

answered Mar 23 '14 at 23:48

Jared Farrish

48,585
17
95
104

How to group rows in a single sql query?

1 Answers1