title: Why aggregate functions can not be used with DISTINCT ON(...)?
body: <p>The question was: </p>
  
  <blockquote>
    <p>How to get row which was selected by aggregate function?</p>
  </blockquote>
  
  <p>The question <a href="https://stackoverflow.com/q/52551186/4632019">was answered</a> and partially resolve my problem. But I still can not replace <code>GROUP BY</code> with <code>DISTINCT ON</code> because of next reason:</p>
  
  <p>I need both: </p>
  
  <ol>
  <li>Select <code>id</code> of aggregated row (may be resolved with <code>DISTINCT ON</code>)</li>
  <li>Sum the <code>ratio</code> column (may be resolved with <code>GROUP BY</code>)</li>
  </ol>
  
  <p>Some <code>amount</code> of resource is consumed by user. One part of day 10h user consumed <code>8</code> another part of day 10h user consumed <code>3</code> and 4h he do not consume resource. The task is to bill consumed resource by the maximum and do not bill when resource was not consumed</p>
  
  <pre><code> id | name | amount | ratio 
  ----+------+--------+-------
    1 | a    |      8 |    10
    2 | a    |      3 |    10
  </code></pre>
  
  <p>I accomplish this task by next query:</p>
  
  <pre><code>SELECT 
      (
         SELECT id FROM t2 
         WHERE id = ANY ( ARRAY_AGG( tf.id ) ) AND amount = MAX( tf.amount ) 
      ) id,
      name, 
      MAX(amount) ma,
      SUM( ratio )
  FROM t2  tf
  GROUP BY name
  </code></pre>
  
  <p><strong>Why it is not allowed to use aggregation functions with <code>DISTINCT ON</code>?</strong></p>
  
  <pre><code>select distinct on ( name ) id, name, amount, sum( ratio )
  from t2
  order by name, amount desc
  </code></pre>
  
  <p>Or even simpler:</p>
  
  <pre><code>select distinct on ( name ) id, name, max(amount), sum( ratio )
  from t2
  </code></pre>
  
  <p>This will resolve also issues with <code>ORDER BY</code>. No need a <a href="https://stackoverflow.com/a/9795768/4632019">workaround with subquery</a></p>
  
  <p><strong>Is there technical reasons which do not allow query from the last example to work as described?</strong></p>
  
  <p><strong>UPD</strong><br>
  In theory this can work like next:</p>
  
  <p>First example:</p>
  
  <pre><code>select distinct on ( name ) id, name, amount, sum( ratio )
  from t2
  order by name, amount desc
  </code></pre>
  
  <p>When the first distinct row found, it saves its <code>id</code> and <code>name</code></p>
  
  <p>Next time when second and next non distinct row is found it will call to <code>sum</code> and accumulate <code>ratio</code></p>
  
  <p>Second example:</p>
  
  <pre><code>select distinct on ( name ) id, name, max(amount), sum( ratio )
  from t2
  </code></pre>
  
  <p>When the first distinct row found, it saves its <code>id</code> and <code>name</code>, accumulate <code>ratio</code> and set current value of <code>ratio</code> as maximum</p>
  
  <p>Next time when second and next non distinct row is found it will call to <code>sum</code> and accumulate <code>ratio</code></p>
  
  <p>If any of second and/or next non distinct row has greater value for <code>ratio</code> column it is saved as <strong>maximum</strong> and saved value for <code>id</code> is updated</p>
  
  <p><strong>UPD</strong><br>
  if <code>more than one row where  amount = max(amount)</code> Postgres can return value from either row. As this is done for any field which is not under DISTINCT ON</p>
  
  <p>To be sure which of is returned the query maybe qualified by <code>ORDER BY</code> clause. Like this is done <a href="https://zaiste.net/postgresql_distinct_on/" rel="nofollow noreferrer">here</a></p>