title: SQL: how can I exclude certain lines from an aggregated result?
body: <p>In the query I built, the result shows something like below:</p>
  <pre><code>SELECT name
        ,ARRAY_AGG(fruits ORDER BY time ASC) AS all_fruits
  FROM table_fruits
  </code></pre>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">name</th>
  <th style="text-align: left;">all_fruits</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">Person A</td>
  <td style="text-align: left;">Apple, Banana, Apple, Apple, Apple, Apple</td>
  </tr>
  <tr>
  <td style="text-align: left;">Person B</td>
  <td style="text-align: left;">Apple, Apple, Apple, Banana, Apple, Banana</td>
  </tr>
  <tr>
  <td style="text-align: left;">Person C</td>
  <td style="text-align: left;">Banana, Banana, Apple, Banana, Apple, Apple</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to add one more column which shows the count of apples. However, I do not want to count apples that are followed by bananas. Therefore, the additional column should look like below.</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">name</th>
  <th style="text-align: left;">all_fruits</th>
  <th style="text-align: left;">count_of_apple</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">Person A</td>
  <td style="text-align: left;">Apple, Banana, Apple, Apple, Apple, Apple</td>
  <td style="text-align: left;">4</td>
  </tr>
  <tr>
  <td style="text-align: left;">Person B</td>
  <td style="text-align: left;">Apple, Apple, Apple, Banana, Apple, Banana</td>
  <td style="text-align: left;">2</td>
  </tr>
  <tr>
  <td style="text-align: left;">Person C</td>
  <td style="text-align: left;">Banana, Banana, Apple, Banana, Apple, Apple</td>
  <td style="text-align: left;">2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>How would I do this in SQL? The source includes time for when the fruit was eaten.</p>