title: Why does this query need aggregate?
body: <p>I am trying to join 2 tables into one table <code>book</code> (they have one-to-one relation) and then nest a one-to-many relation <code>ebooks</code></p>
  
  <p>If I try to nest the one-to-many results it works fine:</p>
  
  <pre><code>select r.*, array_agg(e) e_books from gardner_record r
  left join gardner_e_book_record e on r.ean_number = e.physical_edition_ean
  group by r.id
  </code></pre>
  
  <p>This works as expected. I get the results back but there is a few more columns I need to add to <code>gardner_record</code> from another table with one-to-one relationship called <code>gardner_inventory_item</code></p>
  
  <p>This is what I have tried:</p>
  
  <pre><code>select book.*, array_agg(e) e_books from (
      select  r.*,
             i.price,
             i.discount,
             i.free_stock,
             i.report,
             i.report_date
      from gardner_record r
               inner join gardner_inventory_item i on r.ean_number = i.ean
      ) book
  left join gardner_e_book_record e on book.ean_number = e.physical_edition_ean
  group by book.id
  </code></pre>
  
  <p>But it doesn't work. The query gives error that <code>modified_date</code> (a column on <code>gardner_record</code>) needs to be in group by or aggregated.</p>
  
  <p>Why is this? I make a single table in subquery. Then I left join the nested records as <code>ebooks</code> and use the <code>array_agg()</code> in <code>select</code>. Shouldn't that mean it is all aggregated?  It works in first query.</p>
  
  <p>What am I missing conceptually here?</p>