CODEWITHSUNDEEP
sql
wootscootinboogie
wootscootinboogie

Reputation: 8695

Aggregates in a subquery

I'm fiddling around with creating sub queries just to become accustomed to them. I'm trying to get an ID# and costs associated for a person if their ID appears in a junction table X number of times. I've tried

select id, charges from myTable
where id in
(
    select id from jxnTable
having (count(jxn.id> 10))
group by jxn.id

This gives me the error jxn.id is invalid in the select list because it is not contained in either an aggregate function of a group by clause.

Upvotes: 1

Views: 207

Answers (2)

Mark Byers
Mark Byers

Reputation: 838176

Three errors:

  • The GROUP BY and HAVING must be part of the same query, and the GROUP BY must come textually before the corresponding HAVING.
  • The parentheses around your COUNT expression are incorrect.
  • You have no table or alias called jxn.

Try this instead:

SELECT id, charges
FROM myTable
WHERE id IN
(
    SELECT id
    FROM jxnTable
    GROUP BY id
    HAVING COUNT(id) > 10
)

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270607

Your GROUP BY belongs inside the subquery, in addition to some odd () in your HAVING clause.

select id, charges from myTable
where id in
(
    select id from jxnTable
    GROUP BY id
    having (count(id>) 10)
)

This can also be done with a JOIN against a subquery, rather than an IN() clause, which may be faster:

SELECT m.id, charges
FROM myTable M
  INNER JOIN (
    SELECT id FROM jxnTable
    GROUP BY id
    HAVING COUNT(id) > 10
  ) cnt  ON m.id = cnt.id

Upvotes: 2

Related Questions