sql: COUNT() with conditions

Here’s the skinny, I basically have to periodical parse raw data and save their totals on another table for easier access. In this scenario I basically need to count all raw and unique IP’s from a table and mark them; so when I parse the data again I don’t have to count the values I already totaled.

Below is a sample table containing some data. My goal is to get a count of the unique IP’s that are counted and the total number of raw ip’s that are not counted. This is a much simpler example of what I am trying to achieve on my script, but I hope you get the gist.
01 +----+---------------+---------+
02 | id | ip | counted |
03 +----+---------------+---------+
04 | 1 | 127.0.0.1 | 0 |
05 | 2 | 127.0.0.1 | 1 |
06 | 3 | 192.168.1.100 | 0 |
07 | 4 | 192.168.1.100 | 1 |
08 | 5 | 10.0.0.1 | 0 |
09 | 6 | 10.0.0.1 | 0 |
10 +----+---------------+---------+


Now here’s the query:1 SELECT ip,
2 COUNT(CASE WHEN counted=0 THEN id ELSE NULL END) AS not_counted,
3 COUNT(DISTINCT CASE WHEN counted=1 THEN ip ELSE NULL END) AS is_counted
4 FROM test
5 GROUP BY ip;
1 +---------------+-------------+------------+
2 | ip | not_counted | is_counted |
3 +---------------+-------------+------------+
4 | 10.0.0.1 | 2 | 0 |
5 | 127.0.0.1 | 1 | 1 |
6 | 192.168.1.100 | 1 | 1 |
7 +---------------+-------------+------------