MySQL, confused about nested count

Question

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?

rohitvats · Accepted Answer · 2016-10-16 23:31:18Z

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;

Collectives™ on Stack Overflow

MySQL, confused about nested count

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related