MySQL – Split SELECT query (WHERE IN) into two rows

June 28, 2022

So, I am using MySQL to do a query and have a database like this:

I wanted to do a select query to show every transaction of Bank A and C based on their prefix. This is the expected result:

I have done the query as followed:

SELECT
    M.merk AS 'Merk',
    COUNT( T.amount ) AS 'Jumlah Transaksi',
    SUM( T.amount ) AS 'Total Amount'
FROM
    tb_transaksiatm T
INNER JOIN tb_issuer I ON
    T.nomor_kartu LIKE CONCAT(I.prefix, '%')
INNER JOIN tb_terminalatm M ON
    T.terminal = M.nomor_terminal
WHERE
    I.bank IN ('A', 'C') # Declare the Bank;

But my result is not the same as expected. It combined and summed both rows from Bank A and Bank C. This is my result:

The question is, how do I split the WHERE IN condition into two rows? Any help would be appreciated. Thank you.
Note: The language is Indonesian.

>Solution :

SELECT
    M.merk AS 'Merk',
    COUNT( T.amount ) AS 'Jumlah Transaksi',
    SUM( T.amount ) AS 'Total Amount'
FROM
    tb_transaksiatm T
INNER JOIN tb_issuer I ON
    T.nomor_kartu LIKE CONCAT(I.prefix, '%')
INNER JOIN tb_terminalatm M ON
    T.terminal = M.nomor_terminal
WHERE
    I.bank IN ('A', 'C') # Declare the Bank
group by M.merk;