0

I have this query that is set to return a % but when one of the sender_marks in the list values gets 0%, the mark is not displayed with 0 but disappears.

SELECT  p.SENDER_MARK,ROUND (Removed/TotalTrains*100,2) TrainRemovals
FROM
(SELECT COUNT(DISTINCT TRAIN_ID) AS Removed,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND REMOVED_FROM_DISPATCH_IND = 'Y'
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK ) p,
(SELECT COUNT(DISTINCT TRAIN_ID) AS TotalTrains,SENDER_MARK
FROM BULLETIN.DISPATCH_TRAIN
WHERE SENDER_MARK in ( 'BNSF','NS','UP','METR','BRC','CPRS','CSXT')
AND MODIFIED_TS >= SYSDATE-7 group by SENDER_MARK )b where  p.SENDER_MARK=b.SENDER_MARK
1
1

Your problem is that you are inner joining both subqueries together, meaning that if there are no rows for the join condition (in your case, p.sender_mark = b.sender_mark) in one of the subqueries, you won't get a row in the results for the row in the other subquery that does exist.

You could get around this by turning the join into an outer join, but (IMO) the better option is to use conditional aggregation, like so:

SELECT sender_mark,
       ROUND(removed * 100 / total_trains, 2) trainremovals
FROM   (SELECT sender_mark,
               COUNT(DISTINCT CASE WHEN removed_from_dispatch_ind = 'Y' THEN train_id END) AS removed,
               COUNT(DISTINCT train_id) AS totaltrains
        FROM   bulletin.dispatch_train
        WHERE  sender_mark IN ('BNSF', 'NS', 'UP', 'METR', 'BRC', 'CPRS', 'CSXT')
        AND    modified_ts >= SYSDATE - 7
        GROUP  BY sender_mark);

Aggregate functions (including COUNT, except for when you do COUNT(*)) nearly all exclude null values from their calculations. Therefore by outputting NULL values if a condition is not met, you can get COUNT to ignore those rows.

Doing it like that means you are now only querying the table once instead of twice, which is nearly always going to be more performant.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.