Get The Count Of Rows Count After Group By

Here is the code I use in a Ruby on Rails project to find residences which have amenities with the ids 48, 49 and 50. They are connected with a has_many through connection. id_list

Solution 1:

If your design enforces referential integrity, you don't have to join to the table residences for this purpose at all. Also assuming a UNIQUE or PK constraint on (residence_id, amenity_id) (else you need different queries!)

The best query depends on what you need exactly.

Using a window function, you can even do this in a single query level:

SELECTcount(*) OVER () AS ct
FROM   listed_amenities
WHERE  amenity_id IN (48, 49, 50)
GROUPBY residence_id
HAVINGcount(*) =3
LIMIT  1;

This window function appends the total count to every row without aggregating rows. Consider the sequence of events in a SELECT query:

• Best way to get result count before LIMIT was applied

Accordingly, you could use a similar query to return all qualifying IDs (or even whole rows) and append the count to every row (redundantly):

SELECT residence_id, count(*) OVER () AS ct
FROM   listed_amenities
WHERE  amenity_id IN (48, 49, 50)
GROUPBY residence_id
HAVINGcount(*) =3;

But better use a subquery, that's typically much cheaper:

SELECTcount(*) AS ct
FROM  (
   SELECT1FROM   listed_amenities
   WHERE  amenity_id IN (48, 49, 50)
   GROUPBY residence_id 
   HAVINGcount(*) =3
   ) sub;

You could return an array of IDs (as opposed to the set above) at the same time, for hardly any more cost:

SELECTarray_agg(residence_id ) AS ids, count(*) AS ct
FROM  (
   SELECT residence_id 
   FROM   listed_amenities
   WHERE  amenity_id IN (48, 49, 50)
   GROUPBY residence_id
   HAVINGcount(*) =3
   ) sub;

There are many other variants, you would have to clarify the expected result. Like this one:

SELECTcount(*) AS ct
FROM   listed_amenities l1
JOIN   listed_amenities l2 USING (residence_id)
JOIN   listed_amenities l3 USING (residence_id)
WHERE  l1.amenity_id =48AND    l2.amenity_id =49AND    l2.amenity_id =50;

Basically it's a case of relational division. We have assembled an arsenal of techniques here:

• How to filter SQL results in a has-many-through relation