Menu
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
646
In MySQL, you can use the = operator to test for equality in a query. The = operator can only test equality with values that are not NULL. For example: SELECT * FROM contacts WHERE last_name = 'Johnson';
We can use both SQL Not Equal operators <> and != to do inequality test between two expressions. Both operators give the same output. The only difference is that '<>' is in line with the ISO standard while '!=
Here's the SQL query to compare each row with previous row. In the above query, we join sales table with itself using an INNER JOIN condition g2.id=g1.id + 1 that allows you to compare each row with its previous row. Please note, this condition depends on the fact that our id column has consecutive numbers.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
