Counting rows from a subquery

Simple: I would like to count the number of rows from the sub-query. Note that status is whether the host is online or not.

Bad code

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

Explained

The first query, when run on its own returns this:

SELECT DISTINCT ip_address FROM `ports` WHERE status IS TRUE

ip_address
192.168.1.1
192.168.1.2
192.168.1.248
192.168.1.251
192.168.1.254


The second query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`

17


Question

I would like to know how to count that list of 5 IP addresses.

I have been looking online at possible solutions to this simple problem and just getting frustrated, so thought I'd ask the experts.

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.

StackExchange Database Administrators Q#133384, answer score: 41