title: SQL: using AVG and MAX aggregate functions but still returning duplicates
body: <p>I have a select statement that asks for 4 fields. One is a MAX(date_field), date type, where I want the latest date and the other is the AVG(dollars_field), float type, where I want the function to get the average of these $ amounts and return only one, grouped by their sales order, which is the first field of this query.</p>
  <p><strong>The result comes back with duplicates, as shown below - I've tried distinct, grouping by differently but it still returns duplicates. What am I missing?</strong></p>
  <pre><code>SELECT 
        t1.Name AS [Sales Order No.]
      , t2.name AS [Account Name]
      , (SELECT MAX(t3.Export_Date)) AS [Last Date]  
      , (SELECT AVG(t4.volume_discount)) AS [Rebate AVG]
  </code></pre>
  <p>Then I have the FROM clause with joined tables, and the WHERE clause with some filters, and finally:</p>
  <pre><code>GROUP BY t1.Name, t4.volume_discount, t2.name, t3.Export_Date
  </code></pre>
  <p>This is what it returns:</p>
  <pre><code>Sales Order No. Account Name    Rebate AVG  Last Date
  SO-001      Nova Account Inc    96.000000   2020-06-24
  SO-002      Acme                534.000000  2020-06-29
  SO-002      Acme                534.000000  2020-07-02
  SO-002      Acme                534.000000  2020-09-30
  SO-002      Acme                424.000000  2020-07-23
  SO-003      New Account Co      1603.000000 2020-09-30
  </code></pre>
  <p>Ideally, the SO-002 row would show up once, with the latest date (MAX) and the AVG of all those rebate amounts.</p>