MySQL compares the same values in two columns

Question

MySQL compares the same values in two columns

I have data in my database:

jamu_a | jamu_b | khasiat

A      | B      | Z
A      | B      | X
A      | B      | C

And then, I want the result to be as follows:

jamu_a | jamu_b | khasiat | total

A      | B      | Z, X, C | 3

I am not an expert in MySQL, what is the request for creating such output? Tell me if MySQL cannot do this and need some kind of programming language. thanks in advance

+5

sql database mysql

andrefadila Apr 24 '13 at 8:11

source share

1 answer

John woo · Accepted Answer · 2013-04-24T08:13:01+0000

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 the column KHASIAThas duplicate values and you want it to be unique, you can add DISTINCTtoGROUP_CONCAT()

SELECT  jamu_a,
        jamu_b,
        GROUP_CONCAT(DISTINCT khasiat) khasiat,
        COUNT(*) total
FROM    TableName
GROUP   BY  jamu_a, jamu_b

MySQL compares the same values ​​in two columns

More articles:

MySQL compares the same values in two columns