我有一个带有personid和msg colums的表.
personid, msg
--------------
1, 'msg1'
2, 'msg2'
2, 'msg3'
3, 'msg4'
1, 'msg2'
我想获得每个personid的总消息.
我正在尝试这个查询:
select distinct personid, count(*)
FROM mytable;
我也试过这个查询
select distinct personid, count(msg)
FROM mytable;
但没有得到实际的结果.
我想要以上数据的结果:
id, count
--------
1, 2
2, 2
3, 1
解决方法:
您只需要使用GROUP BY而不是DISTINCT.所以试试这个查询:
SELECT personid, COUNT(msg)
FROM mytable
GROUP BY personid
ORDER BY personid;
GROUP BY
lets you use aggregate functions, likeAVG()
,MAX()
,MIN()
,SUM()
,COUNT()
etc whileDISTINCT
just removes duplicates.