Confront two counts in where clause through subquery

Question

is it legit writing a query structured as this:

select *
from x
where Q1 >= Q2

Q1 and Q2 are both queries which select a count value. In Q1 I extract the count for the single person (for example) and in Q2 I extract a table with a count divided for each person (through a group by).

This link might be useful: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable — Aura, Jun 04 '18 at 15:59
What do you mean by "legit"? Do you mean "will it work"? In which case, have you tried it? Do you mean "is there a more readable way?" or "is there a faster way?" Also, what does this have to do with jQuery? What does your data look like? Right now, this question is pretty meaningless. — IMSoP, Jun 04 '18 at 16:00
@Aureate That question references a term I've never heard of, and links to a Wikipedia page as reference which is now deleted. I'm really struggling to understand how such specific answers were given to such a broad question, so if you know what it means, it would be great if you could edit a better definition into the question for future readers. — IMSoP, Jun 04 '18 at 16:07

score 0 · Accepted Answer · answered Jun 04 '18 at 15:58

0

is it legit writing a query structured as this:

select *
from x
where Q1 >= Q2

Yes, as long as both your queries are scalar queries (probably correlated)

SELECT *
FROM x
WHERE (subquery1) >= (subquery2)

answered Jun 04 '18 at 15:58

Lukasz Szozda

162,964
23
234
275

score 0 · Answer 2 · answered Jun 04 '18 at 15:59

Yes, it is legit. The code would look like:

select x.*
from x
where (select count(. . .) from . . .) >= (select count( . . . ) from . . . );

The parentheses around the subqueries are necessary. A group by would not be appropriate in the subqueries, because the subqueries could then return more than one row. If either returns zero rows, then no rows will be returned, because the value used for the comparison is NULL.

That said, this would be an unusual construct. I would recommend that you ask another question with sample data and desired results to see if there are alternative ways to write the query.

Confront two counts in where clause through subquery

2 Answers2