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.

+----+---------------+---------+
| id | ip            | counted |
+----+---------------+---------+
|  1 | 127.0.0.1     |       0 |
|  2 | 127.0.0.1     |       1 |
|  3 | 192.168.1.100 |       0 |
|  4 | 192.168.1.100 |       1 |
|  5 | 10.0.0.1      |       0 |
|  6 | 10.0.0.1      |       0 |
+----+---------------+---------+

Now here’s the query:

SELECT ip,
	COUNT(CASE WHEN counted=0 THEN id ELSE NULL END) AS not_counted,
	COUNT(DISTINCT CASE WHEN counted=1 THEN ip ELSE NULL END) AS is_counted
	FROM test
	GROUP BY ip;

+---------------+-------------+------------+
| ip            | not_counted | is_counted |
+---------------+-------------+------------+
| 10.0.0.1      |           2 |          0 |
| 127.0.0.1     |           1 |          1 |
| 192.168.1.100 |           1 |          1 |
+---------------+-------------+------------+

Tagged as: code, sql