Mysql query using GROUP_CONCAT()

Question

Using following mysql query:

SELECT DATE_FORMAT(time, '%Y-%m-%d') AS time,report,count(ip) AS countip FROM asn_data WHERE DATE_FORMAT(time, '%Y-%m-%d') > '2012-10-31' GROUP BY DATE_FORMAT(time,'%Y-%m-%d'),report;

I got following output:

+------------+---------------+---------+
| time       | report        | countip |
+------------+---------------+---------+
| 2012-11-01 | bots          |      76 |
| 2012-11-01 | openresolvers |      97 |
| 2012-11-01 | proxy         |      24 |
| 2012-11-01 | scanners      |       4 |
| 2012-11-01 | spam          |     881 |
| 2012-11-02 | bots          |     142 |
| 2012-11-02 | proxy         |      22 |
| 2012-11-02 | spam          |     667 |
| 2012-11-03 | proxy         |       1 |
+------------+---------------+---------+

What will be the query to get the following output:

+------------+-------------+---------------+-------+----------+------+
| time       | bots        | openresolvers | Proxy | scanners | spam |
+------------+-------------+---------------+-------+----------+------+
| 2012-11-01 | 76          | 97            | 24    | 4        | 881  |
| 2012-11-02 | 142         | 0             | 22    | 0        | 667  |
| 2012-11-03 | 0           | 0             | 1     | 0        | 0    |
+------------+-------------+---------------+-------+----------+------+

aleroot · Accepted Answer · 2012-11-03 16:16:06Z

2

You don't need a group concat here, you need to pivot the resultset, try in this way :

SELECT DATE_FORMAT(time, '%Y-%m-%d') AS time,
SUM(CASE WHEN report = 'bots' THEN ipc ELSE 0 END) AS bots,
SUM(CASE WHEN report = 'openresolvers' THEN ipc ELSE 0 END) AS openresolvers,
SUM(CASE WHEN report = 'Proxy' THEN ipc ELSE 0 END) AS Proxy,
SUM(CASE WHEN report = 'scanners' THEN ipc ELSE 0 END) AS scanners,
SUM(CASE WHEN report = 'spam' THEN ipc ELSE 0 END) AS spam
FROM (
SELECT count(ip) AS ipc, report, DATE(time) as time 
FROM  asn_data 
GROUP BY report, DATE(time)) i
WHERE DATE_FORMAT(time, '%Y-%m-%d') > '2012-10-31'
GROUP BY time;

edited Nov 3, 2012 at 16:16

answered Nov 3, 2012 at 15:21

aleroot

73k32 gold badges182 silver badges220 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

eggyal Over a year ago

This answer still attempts to aggregate twice, which will result in Invalid use of group function.

Fakrul Alam Over a year ago

Hi @aleroot: I am getting syntax error. You can try running the code in following schema: sqlfiddle.com/#!2/522f1/2

aleroot Over a year ago

@FakrulAlam see the updated answer and the working SQL Fiddle : sqlfiddle.com/#!2/522f1/27 , i haven't tried the query before, sorry

eggyal · Accepted Answer · 2012-11-03 16:00:13Z

1

SELECT   DATE(time)                  AS time,
         SUM(report='bots'         ) AS bots,
         SUM(report='openresolvers') AS openresolvers,
         SUM(report='proxy'        ) AS Proxy,
         SUM(report='scanners'     ) AS scanners,
         SUM(report='spam'         ) AS spam
FROM     asn_data
WHERE    time > '2012-10-31'
GROUP BY DATE(time)

See it on sqlfiddle.

If you wish to exclude records where ip IS NULL, add AND ip IS NOT NULL into each of the SUM() functions.

edited Nov 3, 2012 at 16:00

answered Nov 3, 2012 at 15:27

eggyal

127k18 gold badges220 silver badges244 bronze badges

2 Comments

aleroot Over a year ago

Fixed the typo error(field separator), How can you be sure that the ip column doesn't exist in the table ??? And again, your answer have the same logic of my answer and changes only the form ...

aleroot Over a year ago

and if the ip field is null ?

Collectives™ on Stack Overflow

Mysql query using GROUP_CONCAT()

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related