title: MYSQL - count(id) with 2 different clause in 1 query (with Inner Join, NOT GROUP BY)
body: <p>I have 2 tables (customer &amp; orders) contains :</p>
  
  <pre><code>// Customer Table
  customer_id     customer_name     customer_address
       1            customer01          address01
       2            customer02          address02
       3            customer03          address03
       4            customer04          address04
       5            customer05          address05
  
  // Orders Table
  order_id    customer_id    order_status
      1             1           rejected
      2             1           success
      3             2           success
      4             1           success
      5             1           pending
      6             2           success
      7             2           pending
      8             3           pending
  </code></pre>
  
  <p>So, in the Order table I had :</p>
  
  <ul>
  <li>customer01 had <code>2 success</code>, <code>1 rejected</code> and <code>1 pending</code></li>
  <li>customer02 had <code>2 success</code> and <code>1 pending</code></li>
  <li>customer03 only had <code>1 pending</code></li>
  </ul>
  
  <p><strong>EXPECTATION :</strong> Result that I need</p>
  
  <pre><code>customer_id   Customer_name (success)   order_status
       1           customer01 (2)           pending
       2           customer02 (2)           pending
       3           customer03 (0)           pending
  </code></pre>
  
  <p><strong>My Query :</strong> using sample data on SQLfiddle</p>
  
  <pre><code>SELECT o.order_id, c.customer_id, CONCAT(c.customer_name, " (", (SELECT COUNT(order_id) FROM orders WHERE order_status = 'success'),")") as "Customer Name (success order)", o.order_status
  FROM customer c
  INNER JOIN orders o ON c.customer_id = o.customer_id
  WHERE o.order_status = 'pending'
  ORDER BY c.customer_id ASC;
  </code></pre>
  
  <p><strong>RESULT</strong></p>
  
  <pre><code>customer_id   Customer_name (success)   order_status
       1           customer01 (4)           pending
       2           customer02 (4)           pending
       3           customer03 (4)           pending
  </code></pre>
  
  <hr>
  
  <p><strong>EDIT</strong> : Remove SQL Fiddle.</p>