I got complicated problem in mysql.
I have some table on mydatabase.
sample docs(this is only simple sample, actual data are so many table that I have to join)
table “merchant”
id name 1 arief 2 john 3 chena
table “transaction”
id product_id price merchant_id date 1 1 20000 2 2020-02-01 2 5 25000 1 2020-02-01 3 2 10000 3 2020-02-02 4 2 10000 2 2020-02-02 5 3 5000 2 2020-02-02 5 2 10000 2 2020-02-03 6 3 5000 3 2020-02-04
I want to know the information of merchants transaction daily “before” and “after” to comparison
like this below
name_merchant sumtrx_20-02-01 sumtrx_20-02-02 sumtrx_20-02-03 sumtrx_20-02-04 arief 1 0 0 0 john 1 2 1 0 chena 0 1 0 1
I tried with this query
select m.name, count(trx.id, trx.date = '2020-02-01') as sumtrx_20-02-01, count(trx.id, trx.date = '2020-02-02') as sumtrx_20-02-02, count(trx.id, trx.date = '2020-02-03') as sumtrx_20-02-03, count(trx.id, trx.date = '2020-02-04') as sumtrx_20-02-04 from merchant as m join transaction as trx on m.id = trx.merchant_id group by m.name
but that query didn’t work
Advertisement
Answer
You can use sum()
instead of count()
.
SELECT m.name, sum(trx.date = '2020-02-01') `sumtrx_20-02-01`, ... sum(trx.date = '2020-02-04') `sumtrx_20-02-04` FROM merchant m INNER JOIN transaction trx ON m.id = trx.merchant_id GROUP BY m.name;
And you also need to enclose identifiers, here the column aliases, in backticks if you use special characters like -
in them. Or avoid using special characters in them all together.