1 Answer
1

This will you give you two counts per ip, one for the rows where code has values 1, 2 or 3 and another count for all the rest (everything but 1, 2, 3, including NULL.)

SELECT ip,
COUNT(CASE WHEN code IN (1, 2, 3) THEN 1 ELSE NULL END) AS cnt_in,
COUNT(CASE WHEN code IN (1, 2, 3) THEN NULL ELSE 1 END) AS cnt_rest
FROM parser
GROUP BY ip
ORDER BY cnt_in DESC ;

This will you give you 3 counts, one for 1,2,3, another for the rest of integer values and a third for rows that have NULL in code:

SELECT ip,
COUNT(CASE WHEN code IN (1, 2, 3) THEN 1 END) AS cnt_in,
COUNT(CASE WHEN code NOT IN (1, 2, 3) THEN 1 END) AS cnt_not_in,
COUNT(CASE WHEN code IS NULL THEN 1 END) AS cnt_null
FROM parser
GROUP BY ip
ORDER BY cnt_in DESC ;

If you want to limit the first result (as your code) to the top 10 rows and the second result to the other top 10 rows, you can use two subqueries and a UNION:

( SELECT ip,
COUNT(*) AS cnt,
'in' AS type
FROM parser
WHERE code IN (1, 2, 3)
GROUP BY ip
ORDER BY cnt DESC
LIMIT 10
)
UNION ALL
( SELECT ip,
COUNT(*) AS cnt,
'not in' AS type
FROM parser
WHERE code NOT IN (1, 2, 3)
GROUP BY ip
ORDER BY cnt DESC
LIMIT 10
) ;