CODEWITHSUNDEEP
sqloracle-databasejoin
lilli
lilli

Reputation: 45

How to do a COUNT with a WHERE clause?

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 :

SELECT t.idCustomer, t.nameCustomer
COUNT(DISTINCT t.idTrip), 
SUM(
  CASE
    WHEN t.tripDone <> 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

So, I want to know for each Customer :

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 <> 1 I get a result of : '2' instead of '1' (because there are 2 records in my from Clause that have the t.tripDone <> 1 for the same idTrip).

Would you have any idea on how I should process this query so I can do the Sum when tripDone <> 1 only once for each tripId ?

Thanks a lot for the help !

If you need some more infos I'm available, and sorry for my lack of english skills !

Upvotes: 2

Views: 89

Answers (2)

Kumar_Ojas
Kumar_Ojas

Reputation: 1

First Select idTrip field in your inner query that means table "t"

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191235

It sounds like you want to do the same count(distinct ...) pattern for the columns you're currently summing, but with some logic. You can use case within a count instead in the same way:

...
COUNT(
  DISTINCT CASE
    WHEN t.tripDone <> 1
    THEN t.idTrip
    ELSE null
  END),
COUNT(
  DISTINCT CASE 
    WHEN t.codeIncident = 'CANCEL'
    THEN t.idTrip
    ELSE null
   END)

The else null is a bit redundant as that's the default. As count() ignores nulls, if the when isn't matched then that trip ID isn't counted.

Upvotes: 1

Related Questions