CODEWITHSUNDEEP
mysqlsqlcountdistinct
Pankaj
Pankaj

Reputation: 71

Get distinct records with counts

I have a table with personid and msg colums. 

personid, msg
--------------
 1,      'msg1'
 2,      'msg2'
 2,      'msg3'
 3,      'msg4'
 1,      'msg2'

I want to get total msg for each personid. I am trying this query:

select distinct personid, count(*)
FROM mytable;

I also tried this query

select distinct personid, count(msg)
FROM mytable;

But not getting actual result. I want this result for above data:

id, count
--------
1,   2
2,   2
3,   1

Upvotes: 3

Views: 256

Answers (1)

Himanshu
Himanshu

Reputation: 32602

You just need to use GROUP BY instead of DISTINCT. So try this query:

SELECT personid, COUNT(msg) 
FROM mytable 
GROUP BY personid
ORDER BY personid;

See this SQLFiddle

GROUP BY lets you use aggregate functions, like AVG(), MAX(), MIN(), SUM(), COUNT() etc while DISTINCT just removes duplicates.

Upvotes: 5

Related Questions