Filter Groups

Filter a grouped table to keep only the groups that meet certain criteria. Note that in this scenario, we are interested in filtering entire groups and not individual rows.

In this example, we wish to keep only groups that have more than 200 rows and where the sum of values of the column col_3 is above 700 for the group.

WITH filtered_groups AS
         (SELECT col_10,
                 COUNT(1)   row_count,
                 SUM(col_3) col_3_sum
          FROM refcon.examples.dummy_table
          GROUP BY col_10
          HAVING row_count > 200
             AND col_3_sum > 700)

SELECT t.*
FROM refcon.examples.dummy_table t
         INNER JOIN filtered_groups g ON g.col_10 = t.col_10;

Here is how this works:

  • We filter the groups separately in the filtered_groups CTE using the HAVING clause.
  • HAVING will only keep groups with row_count > 200 and col_3_sum > 700.
  • We join the grouped table with dummy_table on col_10 using an INNER JOIN.
  • INNER JOIN here acts as a filter and will only keep rows that belong to one of the groups in filtered_groups. See Filtering Joins for more details.
SQL
I/O