I have a data in my database like this :
jamu_a | jamu_b | khasiat
A | B | Z
A | B | X
A | B | C
And then, I want an output like this :
jamu_a | jamu_b | khasiat | total
A | B | Z, X, C | 3
I'm not expert in MySQL, what kind of query to produce an output like that? Tell me if MySQL can't do that and need some programming language. Thanks in advance
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b
OUTPUT
╔════════╦════════╦═════════╦═══════╗
║ JAMU_A ║ JAMU_B ║ KHASIAT ║ TOTAL ║
╠════════╬════════╬═════════╬═══════╣
║ A ║ B ║ Z,X,C ║ 3 ║
╚════════╩════════╩═════════╩═══════╝
if there are repeating values on column KHASIAT and you want it to be unique, you can add DISTINCT on GROUP_CONCAT()
SELECT jamu_a,
jamu_b,
GROUP_CONCAT(DISTINCT khasiat) khasiat,
COUNT(*) total
FROM TableName
GROUP BY jamu_a, jamu_b