CODEWITHSUNDEEP
mysql
Anonymous
Anonymous

Reputation: 1990

get distinct field and their count mysql

I have a table like

A       B        C 
..................
1       2        a
1       3        b
1       4        a

I want the result of query which could give resullt as their distinct value and count Eg. for above table 

result

`{a 2 , b 1}`

I am using sql 5.6.

I tried using GROUP_CONCAT(C) group by A. But I cant store their count this way.

Upvotes: 0

Views: 49

Answers (3)

forpas
forpas

Reputation: 164214

First group to get the counters and then use group_concat():

select 
  concat('{', group_concat(concat(t.C, ' ', t.counter)), '}') result
from (                           
  select C, count(*) counter
  from tablename
  group by C
) t

See the demo. Results:

| result    |
| --------- |
| {a 2,b 1} |

Upvotes: 1

JNevill
JNevill

Reputation: 50308

You'll need a subquery to get your COUNT and distinct c values. From that you can make your GROUP_CONCAT:

CREATE TABLE foo (c char(1));
INSERT INTO foo VALUES ('a'),('a'),('b');
SELECT GROUP_CONCAT(CONCAT(c,' ',countc))
FROM (SELECT c, count(*) as countc FROM foo GROUP BY c) sub;

a 2,b 1

sqldiffle here

Upvotes: 1

Aviza
Aviza

Reputation: 142

You will need something like the following query:

select c, count(c) from <your table name> group by c

Upvotes: 0

Related Questions