0

I have a MySQL table, each row represents a curl request made within php. With the column names (ip, http_code, time_checked)

SELECT * 
FROM proxy_stastistics 
WHERE time_checked > UNIX_TIMESTAMP( NOW() - INTERVAL 24 HOUR )

Im using the above query at the moment to return only the records made in the last 24 hours, but i'm wanting to return only one row per unique IP address but at the say time creating 2 additional columns where any http_code = 200 or http_code = 0 as an increment column.

so i would get something like this

IP          | 200 | 0 | 404
192.168.0.1 | 100 | 5 | 1
192.168.0.2 | 84  | 3 | 0

EDIT after reading up on MySql pivot tables i was able to create the following query to achieve what i wanted to do.

SELECT ip_address, 
       SUM(http_code = '200') AS http200, 
       SUM(http_code = '0')   AS http0, 
       SUM(http_code = '404') AS http404 
FROM   proxy_stastistics 
WHERE  time_checked > Unix_timestamp(Now() - interval 24 hour) 
GROUP  BY ip_address
Neil
  • 95
  • 1
  • 1
  • 7
  • This transformation is called pivoting and has been asked and answered here on SO soooooo many times. The answers to the linked duplicate topic show how to do this transformation within mysql. However, pls note that it may be more efficient to do this transformation in the application logic. – Shadow Nov 11 '18 at 21:32
  • Thanks for pointing me in the right direction, i've found the thread very informative, the problem sometimes is knowing the terminology on what to look for. So thanks again. – Neil Nov 11 '18 at 21:46

1 Answers1

-1

If I understand correctly, you need something like this:

select  ip, 
        sum(if(http_code=200,1,0)) code200 , 
        sum(if(http_code=0,1,0)) code0,  
        sum(if(http_code=404,1,0)) code404 
from proxy_stastistics
where time_checked > UNIX_TIMESTAMP( NOW() - INTERVAL 24 HOUR )
group by ip

You can see working fiddle here:

http://sqlfiddle.com/#!9/35df03/8

Pati
  • 524
  • 7
  • 15