Sfrgttk

What prevents people from lying about where they live in order to reduce state income taxes?

Why do climate experts from the UN/IPCC rarely mention Grand Solar Minimum?

Discouraging missile alpha strikes

"Cheaper by the dozen" phrase origin?

How to use the viewer node?

Manager has noticed coworker's excessive breaks. Should I warn him?

How can I get results over the whole domain of my non-linear differential equations?

Why is it a problem for Freddie if the guys from Munich did what he wanted?

Do error bars on probabilities have any meaning?

Is it appropriate to give a culturally-traditional gift to a female coworker?

Why is autolanding ILS a thing, but not autotakeoffing ITS?

Should you blow through the final approach course if the ATC forgot about you?

How bad is a Computer Science course that doesn't teach Design Patterns?

How to play song that contains one guitar when we have two guitarists (or more)?

Why is Bernie Sanders maximum accepted donation on actblue $5600?

What have we got?

How can a kingdom keep the secret of a missing monarchy from the public?

Is layered encryption more secure than long passwords?

Does the race of half-elves on Khorvaire in Eberron have its own name?

Pictures from Mars

Is a 1hr 3min layover at ORD (Chicago) enough when coming in international?

Is there any physical or computational justification for non-constructive axioms such as AC or excluded middle?

What is the Guild Die for?

Why does calling Python's 'magic method' not do type conversion like it would for the corresponding operator?

Count Total Transaction each specific row

Limit WHERE to MAX() & COUNT()Subqueries run very fast individually, but when joined are very slowhow to group transaction entries?Count from a count (distinct)Using outer alias in a subqueryPostgres - How to get Multi-Count Query using a Where Clause for a Join TableMySQL get previous row with inner joinIs there any ways to get the row count of joined tables?Allocate each value, considering what would be insertedPayment method schema design

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!

mysql join

share

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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!

mysql join

share

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

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!

mysql join

share

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

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;

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

share

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 mins ago

CyrilCyril

1

New contributor

Cyril is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 8 mins ago

CyrilCyril

1