vendredi 27 mars 2015

SQL Union "All Other" Row

I've got a Sqlite database with close to 500,000 rows worth of access log information in it. I'm using it for aggregate information like "number of times each ip has hit the site", or "percentage of hits were POST", etc.


I wrote a SQL query that gathers how many times each IP address has hit the site, where the number of occurrences is greater than 1% of the count of the IP addresses.



select ip_address, count(ip_address)
from records
group by ip_address
having count(ip_address) > (select count(ip_address) from records) * .01


This returns about 7 significant IP addresses. How would I go about unioning an "All Others" row to the result set?


I tried UNIONing with the logical opposite



select "All Others", count(ip_address)
from records
group by ip_address
having count(ip_address) < (select count(ip_address) from records) * .01


but this returns multiple "All Other" rows, with a count that is sequential.


Aucun commentaire:

Enregistrer un commentaire