Combining individual select aggregate queries into a single query

I’m trying to optimise some SQL queries in a script of mine that uses a SQLite database. Currently I loop through creating multiple queries as below to get aggregate data for a list of IP addresses. Is there a way to run this as one query instead? Desired output is the “top 20 domains” for each IP.

SELECT ipaddr, domain, Count(domain) Count
FROM tablea
WHERE (ipaddr = 'IP')
GROUP BY domain
ORDER BY COUNT(domain) DESC
LIMIT 20;

Current results, with 1 query run per IP. I’d like to combine these into 1 query if possible.

IP1|DOMAIN1|COUNT
IP1|DOMAIN2|COUNT
IP2|DOMAIN3|COUNT
IP2|DOMAIN4|COUNT

Answer

You must group by ipaddr and domain and use ROW_NUMBER() (or RANK()) window function to rank the the domains:

SELECT ipaddr, domain, Count
FROM (
  SELECT ipaddr, domain, COUNT(*) Count,
         ROW_NUMBER() OVER (PARTITION BY ipaddr ORDER BY COUNT(*) DESC) rn
  FROM tablea
  WHERE ipaddr IN ('IP1', 'IP2', 'IP3', ...)
  GROUP BY ipaddr, domain
)
WHERE rn <= 20 
ORDER BY ipaddr, Count DESC