MySQL - Split SELECT query (WHERE IN) into two rows-CodePudding

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.

CodePudding user response：

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;

CodePudding user response：

When you use an aggregation function such as SUM or COUNT and you do not specify a GROUP BY, it will aggregate all rows together. Fields such as M.Merk that could vary between the rows being aggregated will have a value taken from an arbitrary one of the rows being aggregated, though modern versions of mysql default to an ONLY_FULL_GROUP_BY mode where selecting such a field will result in an error instead of an arbitrary value.

It sounds like you intend to have a GROUP BY I.Bank, M.Merk, though it is confusing that you don't include the bank in your selected fields.