Skip to main content
Database Administrators

Return to Question

Tweeted twitter.com/StackDBAs/status/1257641214129647616
added 71 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 424
  • 663

Counting rows from a SubQuerysubquery

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

Bad code: ###Bad code

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

Explained: ###Explained

1stThe 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

ip_address  
192.168.1.1
192.168.1.2
192.168.1.248
192.168.1.251
192.168.1.254

2ndThe second query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

17

Question: Just would like to know how to count that list of 5 IP addreses. ###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.

Counting from a SubQuery

Simple: I would like to count the number of rows from the sub-query. Note that status it 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:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

Question: Just would like to know how to count that list of 5 IP addreses.

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

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.

deleted 2 characters in body; edited title
Source Link
Julien Vavasseur
Julien Vavasseur
  • 10.2k
  • 2
  • 28
  • 46

Counting from a SubQuery

Simple: I would like to count the number of rows offrom the sub-query. Note that status it 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:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

Question: Just would like to know how to count that list of 5 IP addreses.

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

Counting a SubQuery

Simple: I would like to count the number of rows of the sub-query. Note that status it 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:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

Question: Just would like to know how to count that list of 5 IP addreses.

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

Counting from a SubQuery

Simple: I would like to count the number of rows from the sub-query. Note that status it 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:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

Question: Just would like to know how to count that list of 5 IP addreses.

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

minor cleanup
Source Link
Andriy M
Andriy M
  • 23.2k
  • 6
  • 59
  • 103

Counting a SubQuery MySQL

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

.

Bad code:

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

.

Explained:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

.

Question: Just would like to know how to count that list of 5 IP addreses.

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

Counting a SubQuery MySQL

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

.

Bad code:

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

.

Explained:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

.

Question: Just would like to know how to count that list of 5 IP addreses.

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

Counting a SubQuery

Simple: I would like to count the number of rows of the sub-query. Note that status it 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:

1st query 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

2nd query run on its own returns this:

SELECT COUNT(ip_address) FROM `ports`
17

Question: Just would like to know how to count that list of 5 IP addreses.

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

Source Link
user90537
user90537