Count Total Transaction each specific row even if 0

Question

I have a 3 Columns namely:

1. Payment Channel
2. Payment Name
3. Total Transaction

The Total Transaction will be the COUNT of each Payment Channel and Payment Name.

In the Payment Channel, values have PC01-PC09.

I want my result to display all the Payment Channel even if no transactions.

Here is my SQL Script:

SELECT
    B2C_BUY_LOG.PG_CHANNEL AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM B2C_BUY_LOG
INNER JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER JOIN COMM_CODE ON B2C_BUY_LOG.PG_CHANNEL = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
AND COMM_CODE.CODE IN('PC01', 'PC02', 'PC03', 'PC04', 'PC05', 'PC06', 'PC07', 'PC08', 'PC09')
GROUP BY Payment_Channel, Payment_Name;

Here is the result of my Query:

Payment_Channel   Payment_Name    Total_Transaction
PC01              Name-1          14
PC02              Name-2          2
PC03              Name-3          7
PC04              Name-4          9
PC06              Name-6          21
PC08              Name-8          18
PC09              Name-9          95

This query returns only the match values between the joined tables because it is INNER JOIN and the PC05 and PC07 is missing on the result because it has no transaction. I also tried different JOINS.

How can I display the PC05 and PC07 with a count of 0 if no transaction?

Thanks!

Answer 1

SELECT
    channels.channel AS Payment_Channel,
    COMM_CODE.CODE_NAME AS Payment_Name,
    COUNT(B2C_BUY_LOG.PAY_ID) AS Total_Transaction
FROM (           SELECT 'PC01' channel 
       UNION ALL SELECT 'PC02' 
       UNION ALL SELECT 'PC03' 
       UNION ALL SELECT 'PC04' 
       UNION ALL SELECT 'PC05' 
       UNION ALL SELECT 'PC06' 
       UNION ALL SELECT 'PC07' 
       UNION ALL SELECT 'PC08' 
       UNION ALL SELECT 'PC09') channels
LEFT JOIN B2C_BUY_LOG ON B2C_BUY_LOG.PG_CHANNEL = channels.channel
LEFT /* or INNER */ JOIN B2C_BUY_HIST ON B2C_BUY_LOG.PAY_ID = B2C_BUY_HIST.PAY_ID
INNER /* or LEFT */ JOIN COMM_CODE ON B2C_channels.channel = COMM_CODE.CODE
WHERE B2C_BUY_LOG.RET_CODE = 1
AND B2C_BUY_LOG.PAY_ID LIKE '190220%'
GROUP BY Payment_Channel, Payment_Name;