Counting rows from a subquery

Question

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.

Answer 1

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.

Answer 2

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');

Answer 3

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