-3

i'm new to Mysql and SO,let me know if my question is not clear

i have a table like this 

key | value
-----------------------
key1  | value3
key1  | value3
key1  | value1
key3  | value4
key3  | value2
key4  | value1
key4  | value1
key5  | value2

i need count of each distinct value that matched with unique keys.

For EX: i need o/p like 

( 
 (key1,value1:1,value3:2),
 (key3,value2:1,value4:1),
 (key4,value1:2),
 (key5,value2:1,)
)

can anyone suggest me the query.

Edit: I used group by like

select value, count(*) from table GROUP BY value;

but it's not giving proper results.

Narendra Lucky
  • 340
  • 2
  • 13
  • So you need concatenated values for each grouped keys? – mad_ Aug 08 '18 at 15:01
  • Possible duplicate of [Finding duplicate values in a SQL table](https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table) – Ines Tlili Aug 08 '18 at 15:23

1 Answers1

0

I just tested this query with the same table you gave and it gave the results you are asking for 

SELECT key, value, COUNT(*)
FROM your_table
GROUP BY
key, value
HAVING 
COUNT(*) >= 1
ORDER BY key
Ines Tlili
  • 790
  • 6
  • 21