title: Aggregate and count after left join
body: <p>I am aggregating columns of a table to find the count of unique values. For example, aggregating
  the status shows that out of 5 alerts there are 2 in open status and 3 that are closed. The simplified table looks like this:</p>
  <pre><code>create table alerts (
    id,
    status,
    owner_id
  );
  </code></pre>
  <p>The query below uses grouping sets to aggregate multiple columns at once. This approach works well.</p>
  <pre><code>with aggs as (
  select status
      from alerts
      where alerts.owner_id = 'x'
  )
     select status, count(*)
     from aggs
     group by grouping sets(
        (),
        (status)
     );
  </code></pre>
  <p>the output at its simplest could look like this:</p>
  <pre><code> status | count
  --------+-------
          |     1
   new    |     1
  </code></pre>
  <p>However, now I need to aggregate additional columns from another table. This table (shown below) can have zero or more rows associated to the first table (alerts:users 1:N).</p>
  <pre><code>create table users (
    id,
    alert_id,
    name
  );
  </code></pre>
  <p>I have tried updating the query to use a left join but this approach incorrectly inflates the counts of the alert columns.</p>
  <pre><code>with aggs as (
  select alerts.status, users.name
      from alerts
      left join users on alerts.id = users.alert_id
      where alerts.owner_id = 'x'
     --  and additional filtering by columns in the users table
  )
     select status, name, count(*)
     from aggs
     group by grouping sets(
        (),
        (status),
        (name)
     );
  </code></pre>
  <p>Below is an example of the incorrect results. Since there are 3 rows in the user table the count
  for the status column is now 3 but should be 1.</p>
  <pre><code> status |          name           | count
  --------+-------------------------+-------
          |                         |     3
          | user1                   |     1
          | user2                   |     1
          | user3                   |     1
   new    |                         |     3
  </code></pre>
  <p>How can I perform this aggregation to include the columns from the table with a many-to-one relationship without inflating the counts? In the future I will likely need to aggregate more columns from other tables with a many-to-one relationship and need a solution that will still work with several left joins. All help is much appreciated.</p>
  <p>edit: link to db-fiddle <a href="https://www.db-fiddle.com/f/buGD2DuJiqf9LGF9rw5EgT/2" rel="nofollow noreferrer">https://www.db-fiddle.com/f/buGD2DuJiqf9LGF9rw5EgT/2</a></p>