vendredi 29 novembre 2019

Count all joined table rows when filtering by the same joined table

So I'm querying a table districts, that has one to many relationship with the positions table. I want to select the districts that have the positions with particular name AND count ALL districts positions at the same time. Can I do this?

With the current code

 SELECT districts.*, COUNT(DISTINCT(positions.id)) as positions_count FROM "districts"
  LEFT JOIN positions ON positions.id = districts.position_id
 WHERE ("positions"."name" IN ($1, $2)) GROUP BY districts.id ORDER BY positions_count desc, "districts"."name" ASC

If I have 20 positions in some district but only 2 are filtered, positions_count eq 2 as well, I want it to be 20

I've tried to join twice on the same table with an alias but this gives me the same result

SELECT districts.*, COUNT(DISTINCT(positions_to_count.id)) as positions_count FROM "districts"
  LEFT JOIN positions ON positions.id = districts.position_id
  LEFT JOIN positions AS positions_to_count ON positions_to_count.id = districts.position_id WHERE ("positions"."name" IN ($1, $2)) GROUP BY districts.id ORDER BY positions_count desc, "districts"."name" ASC



Aucun commentaire:

Enregistrer un commentaire