CODEWITHSUNDEEP
mysqlsql
Andrew
Andrew

Reputation: 7545

MySQL, confused about nested count

Here is the table:

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  123 | Amy    |  3.9 |   1000 |
|  234 | Bob    |  3.6 |   1500 |
|  345 | Craig  |  3.5 |    500 |
|  456 | Doris  |  3.9 |   1000 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  987 | Helen  |  3.7 |    800 |
|  876 | Irene  |  3.9 |    400 |
|  765 | Jay    |  2.9 |   1500 |
|  654 | Amy    |  3.9 |   1000 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

I can't figure out what the logic is behind this query

select *
from Student S1
where (select count(*) from Student S2
   where S2.sID <> S1.sID and S2.GPA = S1.GPA) =
  (select count(*) from Student S2
   where S2.sID <> S1.sID and S2.sizeHS = S1.sizeHS);

This is what is returned:

+------+--------+------+--------+
| sID  | sName  | GPA  | sizeHS |
+------+--------+------+--------+
|  345 | Craig  |  3.5 |    500 |
|  567 | Edward |  2.9 |   2000 |
|  678 | Fay    |  3.8 |    200 |
|  789 | Gary   |  3.4 |    800 |
|  765 | Jay    |  2.9 |   1500 |
|  543 | Craig  |  3.4 |   2000 |
+------+--------+------+--------+

How is count, an aggregation command, able to equal another aggregation and return a table when it is a where criteria?

Upvotes: 0

Views: 78

Answers (1)

rohitvats
rohitvats

Reputation: 1851

The count(*) queries are being run as co-related sub-queries and they both return a single scalar value (an integer). Your main query does not have any aggregation of it's own.

The two count(*) queries return two numbers, which are being compared to each other in the where condition which is perfectly legal.

The query would evaluate to something like this:

select *
from Student S1
where (<count of students with the same GPA as this student>) 
        =
      (<count of students with the same sizeHS as this student>);

And then, for example, if the counts for a student (one record in the table) come back as 5 and 6, then the where condition for that record will be evaluated as:

select *
from Student S1
where 5 
        =
      6;

Upvotes: 3

Related Questions