title: Calculating average count on each row in Postgres 11.7 without a CROSS JOIN
body: <p>I'm trying to figure out the best way to attach an overall average of a count to each row in Postgres 11.7. I'm frequently interested in the average of a count, but have found it unexpectedly awkward to calculate in SQL. I suspect I'm missing something pretty basic. I've set up a toy table to work with:</p>
  
  <pre><code>BEGIN:
  
  DROP TABLE IF EXISTS progress CASCADE;
  
  CREATE TABLE IF NOT EXISTS progress (
      package_id     integer     NOT NULL DEFAULT NULL,
      completed_dts  timestamptz NOT NULL DEFAULT NULL,
      step           citext      NOT NULL DEFAULT NULL);
  
  INSERT INTO progress (package_id, completed_dts, step) VALUES
      (1,'2020-01-01 01:00:00.000000+10','Assemble'),
      (1,'2020-01-01 01:15:00.000000+10','Inspect'),
      (1,'2020-01-01 01:25:00.000000+10','Box'),
  
      (2,'2020-01-01 01:00:00.000000+10','Pull'),
      (2,'2020-01-01 01:10:00.000000+10','Inspect'),
      (2,'2020-01-01 01:13:00.000000+10','Dispatch'),
  
      (3,'2020-01-01 01:00:00.000000+10','Assemble');
  
  COMMIT;
  </code></pre>
  
  <p>There are three packages in progress, <code>1</code>, <code>2</code>, and <code>3</code>. Both <code>1</code> and <code>2</code> have three steps, while <code>3</code> has one step. So, the average is <code>7/3 = 2.333...</code>. This is the target output:</p>
  
  <pre><code>package_id    step_count    step_avg
  1             3             2.3333333333333333
  2             3             2.3333333333333333
  3             1             2.3333333333333333
  </code></pre>
  
  <p>I've hunted down a lot of threads here, and examples elsewhere. It looks like the overall average can be calculated using a subquery or CTE. Here's the CTE version:</p>
  
  <pre><code>WITH steps AS (
    select package_id,
           count(*) AS step_count      
  
      from progress
  
   group by package_id)
  
  select avg(step_count) from steps;
  </code></pre>
  
  <p>Here's the subquery alternative:</p>
  
  <pre><code>select avg(total_steps) 
    from 
       (  select count(*) AS total_steps 
          from progress 
      group by package_id) AS steps_count
  </code></pre>
  
  <p>It <em>feels</em> like what I want is a window function, something like this:</p>
  
  <pre><code>   select package_id,
            count(*) as step_count,
            avg(count(*) OVER (PARTITION BY package_id)) as step_count_avg
  
       from progress 
  
   group by package_id
   order by package_id
  </code></pre>
  
  <p>This does not work:</p>
  
  <pre><code>ERROR:  aggregate function calls cannot contain window function calls
  LINE 3:           avg(count(*) OVER (PARTITION BY package_id)) as st...
                        ^. (Line 9)
  </code></pre>
  
  <p>I've not found any way to combine the average with my grouping query short of a <code>CROSS JOIN</code>. This produces the correct output:</p>
  
  <pre><code>-- Group the records and get the count(*).
  WITH 
  steps AS (
    select package_id,
           count(*) AS step_count      
  
      from progress
  
   group by package_id),
  
  -- Calculate the step average.
   step_summary AS (
     select avg(step_count) AS step_avg from steps
     )
  
  -- CROSS JOIN to agglutinate the results
    select steps.package_id,
           steps.step_count,
           step_summary.step_avg
  
      from steps,
           step_summary
  
  order by package_id
  </code></pre>
  
  <p>This seems a bit over the top. Is there a more straightforward and efficient way to get an overall value like this? </p>
  
  <p>For background:
  * Our real tables will have 10s of millions of rows. (Only 2.5M now for a test data set.)</p>
  
  <ul>
  <li><p>I'm only looking at <code>avg(count())</code> here, but we've got other examples of <code>aggregate(aggregate())</code> to implement as well.</p></li>
  <li><p>Simply attaching the average to a combined output without the <code>CROSS JOIN</code> also seemed to fail completely.</p></li>
  <li><p>We're generating output like this for import into Domo, which likes redundant values on each row.</p></li>
  <li><p>Yes, we can use temp tables, or rollup tables, if necessary. But I'm also keen to figure out the simplest way to integrate <code>aggregate(aggregate())</code> results in queries generally.</p></li>
  </ul>
  
  <h1>Suggestion Follow-up</h1>
  
  <p>Here's a link to an old Oracle thread with a really close question:</p>
  
  <p><a href="https://stackoverflow.com/questions/800347/sql-avgcount">SQL AVG(COUNT(*))?</a></p>
  
  <p>If I've understood that thread correctly, here's a rewrite of my query with two CTEs and no <code>CROSS JOIN</code>:</p>
  
  <pre><code>with 
  step_total as 
  (
      select package_id, count(*) as step_count
      from progress
      group by package_id
  ),
  
  step_stats as
  (
      select package_id, 
             step_count, 
             avg(step_count) over (partition by package_id) as average
  
      from step_total
  )
  
  select *
  from step_stats
  </code></pre>
  
  <p>This provides an average <em>per row</em>, which is going to equal the <code>count(*)</code>, in this case. What I'm trying to get is the <em>overall</em> average of <code>2.3333....</code> bolted onto each row. Here's what the query above returns for me:</p>
  
  <pre><code>package_id  step_count  average
  1           3           3
  2           3           3
  3           1           1
  </code></pre>