title: How to count on join a table with 2 conditions?
body: <p>I have an <code>items</code> table</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Nganu</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Kae</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Lho</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Also I have an <code>item_usages</code> table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>item_id</th>
  <th>user_id</th>
  <th>usage_time</th>
  <th></th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>99</td>
  <td>2021-10-07 00:00:00</td>
  <td></td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>99</td>
  <td>2021-10-07 00:00:00</td>
  <td></td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>99</td>
  <td>2021-10-08 00:00:00</td>
  <td></td>
  </tr>
  <tr>
  <td>4</td>
  <td>1</td>
  <td>22</td>
  <td>2021-10-08 00:00:00</td>
  <td></td>
  </tr>
  <tr>
  <td>5</td>
  <td>3</td>
  <td>22</td>
  <td>2021-10-08 00:00:00</td>
  <td></td>
  </tr>
  <tr>
  <td>6</td>
  <td>1</td>
  <td>99</td>
  <td>2021-10-08 00:00:00</td>
  <td></td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I want to find an item's total usage and user usage in a query. an example I would like to find user_id 99 usage, expected result:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>total_usage</th>
  <th>user_usage</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2</td>
  <td>Kae</td>
  <td>1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Nganu</td>
  <td>4</td>
  <td>3</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Lho</td>
  <td>1</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I tried:</p>
  <pre><code>select 
      &quot;items&quot;.*,
      count(total_usage.id) as total_usage, 
      count(user_usage.id) as user_usage 
  from 
      &quot;items&quot;
  left join 
      &quot;item_usages&quot; as &quot;total_usage&quot; on &quot;items&quot;.&quot;id&quot; = &quot;total_usage&quot;.&quot;item_id&quot;
  left join 
      &quot;item_usages&quot; as &quot;user_usage&quot; on &quot;user_usage&quot;.&quot;item_id&quot; = &quot;items&quot;.&quot;id&quot; 
                                    and &quot;user_usage&quot;.&quot;user_id&quot; = 99
  group by 
      &quot;items&quot;.&quot;id&quot;;
  </code></pre>
  <p>but it returns:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>total_usage</th>
  <th>user_usage</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>2</td>
  <td>Kae</td>
  <td>1</td>
  <td>1</td>
  </tr>
  <tr>
  <td>1</td>
  <td>Nganu</td>
  <td>12</td>
  <td>12</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Lho</td>
  <td>1</td>
  <td>0</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><code>item_usages</code> only have 6 rows, why Nganu have 12 on both usage? How to fix my query?</p>
  <p>I tried on PostgreSQL 12.8 and 13.4, I also tested on SQLFiddle(PostgreSQL 9.6), Here is the link:</p>
  <p><a href="http://sqlfiddle.com/#!17/f1aac/5" rel="nofollow noreferrer">http://sqlfiddle.com/#!17/f1aac/5</a></p>
  <p>I got the query that returned the correct result:</p>
  <pre><code>select 
      &quot;items&quot;.*,
      min(total_usage.total_count) as total_usage, 
      count(user_usage.id) as user_usage 
  from &quot;items&quot;
  left join 
      (select item_id,count(item_id) as total_count  from item_usages group by item_id) as total_usage 
       on &quot;items&quot;.&quot;id&quot; = &quot;total_usage&quot;.&quot;item_id&quot;
  left join &quot;item_usages&quot; as &quot;user_usage&quot; 
       on &quot;user_usage&quot;.&quot;item_id&quot; = &quot;items&quot;.&quot;id&quot; and &quot;user_usage&quot;.&quot;user_id&quot; = 99
   group by &quot;items&quot;.&quot;id&quot;;
  </code></pre>
  <p>But I don't know about the performance, so  I still find faster query if possible and still wondering:</p>
  <p><strong>Why does my first query give wrong result?</strong></p>