title: How to do a COUNT with a WHERE clause?
body: <p>I actually have a query joining 3 tables to collect informations so I can calculate some KPIs, but my logic is flawed, here is my actual query :</p>
  
  <pre><code>SELECT t.idCustomer, t.nameCustomer
  COUNT(DISTINCT t.idTrip), 
  SUM(
    CASE
      WHEN t.tripDone &lt;&gt; 1
      THEN 1
      ELSE 0
    END),
  SUM(CASE 
      WHEN t.codeIncident = 'CANCEL'
      THEN 1
      ELSE 0
     END)
  FROM 
  (SELECT customer.idCustomer, customer.nameCustomer, trip.tripDone, incident.codeIncident
  FROM CUSTOMER customer
  JOIN TRIP trip ON customer.idCustomer = trip.idCustomer
  JOIN INCIDENT incident ON trip.idTrip = incident.idTrip) t
  GROUP BY t.idCustomer, t.nameCustomer
  </code></pre>
  
  <p>So, I want to know for each Customer :</p>
  
  <ul>
  <li>COUNT(DISTINCT t.idTrip) -> The number of trips by this customer</li>
  <li>Sum when t.tripDone &lt;> 1 -> The number of trips that are done by this customer ( not ingoing )</li>
  <li>Sum when t.codeIncident = 'CANCEL' -> The number of trips by this customer where there was a cancellation.</li>
  </ul>
  
  <p>The big mistake I made here, is that a trip can have multiple codeIncidents (example : one record for an idTrip with the codeIncident 'CANCEL' and another record with same idTrip with the codeIncident 'DELAYED'), so when I calculate the Sum when t.tripDone &lt;> 1 I get a result of : '2' instead of '1' (because there are 2 records in my from Clause that have the t.tripDone &lt;> 1 for the same idTrip).</p>
  
  <p>Would you have any idea on how I should process this query so I can do the Sum when tripDone &lt;> 1 only once for each tripId ?</p>
  
  <p>Thanks a lot for the help ! </p>
  
  <p>If you need some more infos I'm available, and sorry for my lack of english skills !</p>