CODEWITHSUNDEEP
mysqlsqldatabase
andrefadila
andrefadila

Reputation: 687

MySQL compare same values in two column

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

Upvotes: 5

Views: 2503

Answers (1)

John Woo
John Woo

Reputation: 263683

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

Upvotes: 10

Related Questions