No one pointed out the actual problem with the Bad code so: You need to name the inner SELECT's output using AS some_name

SELECT COUNT(ip_address) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS some_name

As mentioned already, best is:

SELECT COUNT(DISTINCT ip_address) FROM `ports`;

---

Why?

See: https://stackoverflow.com/questions/3363918/mysql-error-1248-42000-every-derived-table-must-have-its-own-alias

On MySQL 5.7 you get ERROR "[42000][1248] Every derived table must have its own alias" without AS some_name. With it, it works.secs ago

To answer your immediate question, how to count rows of a subquery, the syntax is as follows:

SELECT COUNT(*) FROM (subquery) AS some_name;

The subquery should immediately follow the FROM keyword. (In MySQL it is also mandatory to assign a name to a subquery of this kind (it is actually called a derived table), which is why you can see the AS some_name following it.) The way you have written it, MySQL interprets your script as two independent queries, that is why you are getting two result sets.

So, since the subquery in your case is

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

the complete query would look like this:

SELECT COUNT(*) FROM (
    SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE
) AS derived;

But, as Julien has suggested, you can rewrite your query just like this:

SELECT COUNT(DISTINCT ip_address) FROM `ports` WHERE status IS TRUE;

This way you do not need a subquery/derived table at all, because with the DISTINCT keyword the COUNT function will count only distinct occurrences of ip_address in the ports table.

You must move the DISTINCT to the COUNT():

SELECT COUNT(DISTINCT ip_address) FROM `ports`;

This returns 5 because it only counts distinct values and the subquery is not needed anymore.

However this query returns 17 because there are 17 rows in the portstable:

SELECT COUNT(ip_address) FROM `ports`;

See this SQL Fiddle.

Sample data with 17 rows and 5 distinct IPs:

CREATE TABLE ports (ip_address varchar(20));

INSERT INTO `ports`(ip_address) VALUES
  ('192.168.1.1')
  , ('192.168.1.1')
  , ('192.168.1.1')
  , ('192.168.1.2')
  , ('192.168.1.2')
  , ('192.168.1.2')
  , ('192.168.1.2')
  , ('192.168.1.248')
  , ('192.168.1.248')
  , ('192.168.1.248')
  , ('192.168.1.248')
  , ('192.168.1.251')
  , ('192.168.1.251')
  , ('192.168.1.251')
  , ('192.168.1.254')
  , ('192.168.1.254')
  , ('192.168.1.254');