0

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!

  • I already used LEFT JOIN but it doesn't show also – Cyril Feb 20 at 8:51
  • Put your WHERE criteria into the ON and then LEFT JOIN – danblack Feb 20 at 8:59
  • is there actually PC05 and PC07 in the data set that match the crieria – danblack Feb 20 at 9:00
  • what do you mean @danblack – Cyril Feb 20 at 9:01
0
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;
  • I tried this but the PC05 and PC07 doesn't show. Also, tried with the COUNT(COMM_CODE.CODE) AS Total_Transaction, didnt work – Cyril Feb 20 at 9:01
  • @Cyril PC05 and PC07 is missing on the result because it has no transaction Does this mean there is no those codes in B2C_BUY_LOG table? If so you need a synthetic base table (or maybe such table which stores all codes already exists?) contained all codes you need to show as a base table. On that step specify MySQL version in your question (proper tagging recommended). – Akina Feb 20 at 9:05
  • there is value in the COMM_CODE.CODE table. The PC05 and PC07 is there, the COMM_CODE.CODE table and B2C_BUY_LOG.PG_CHANNEL is fk – Cyril Feb 20 at 9:08
  • @Cyril Check the edited variant. Non-commented join variants seems to be preferable, but it must be checked due to a logic unknown to me. – Akina Feb 20 at 9:12
  • its syntax error – Cyril Feb 20 at 9:29

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.