SQL Query that relies on COUNT from another table? - SQLAlchemy

Question

I need a query that can return the records from table A that have greater than COUNT records in table B. The query needs to be able to go in line with other filters that might be applied on table A.

Example case study:

I have a person and appointments table. I am looking for all people who have been in for 5 or more appointments. It must also support extra filter statements on the person table, such as age > 18.

EDIT -- SOLUTION

subquery = db.session.query(Appointment.id_person, 
                            func.count('*').label('person_count')) \
                     .group_by(Appointment.id_person).subquery()
qry = db.session.query(Person) \
                .outerjoin((subquery, Person.id == subquery.c.id_person)) \
                .order_by(Person.id).filter(subquery.c.person_count >= 5).filter(Person.dob <= '1992-10-29')

Kevin Stricker · Accepted Answer · 2010-10-30 04:22:11Z

1

Use a subquery:

SELECT * from person
WHERE PersonID IN 
  (SELECT PersonId FROM appointments
   GROUP BY PersonId
   HAVING COUNT(*) >= 5)
AND dob > 25

answered Oct 30, 2010 at 4:22

Kevin Stricker

17.4k5 gold badges49 silver badges72 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kevin Stricker Over a year ago

I'm glad I could help despite my apparent lack of reading comprehension.

alecxe · Accepted Answer · 2013-06-27 22:17:00Z

0

SELECT Person.PersonID, Person.Name
FROM Person INNER JOIN Appointment
ON Person.PersonID = Appointment.PersonID
GROUP BY Person.PersonID, Person.Name
HAVING COUNT(Person.PersonID) >= 5

edited Jun 27, 2013 at 22:17

alecxe

476k127 gold badges1.1k silver badges1.2k bronze badges

answered Oct 30, 2010 at 12:26

Subhash

2611 silver badge2 bronze badges

Collectives™ on Stack Overflow

SQL Query that relies on COUNT from another table? - SQLAlchemy

EDIT -- SOLUTION

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

EDIT -- SOLUTION

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related