使用计数获取不同的记录 [英] Get distinct records with counts
本文介绍了使用计数获取不同的记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个有 personid
和 msg
列的表格。
I have a table with personid
and msg
colums.
personid, msg
--------------
1, 'msg1'
2, 'msg2'
2, 'msg3'
3, 'msg4'
1, 'msg2'
我想为每个 personid $>获得
msg
c $ c>。
我正在尝试这个查询:
I want to get total msg
for each personid
.
I am trying this query:
select distinct personid, count(*)
FROM mytable;
我也尝试过这个查询
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
推荐答案
您只需使用 GROUP BY
而不是 DISTINCT
。所以尝试这个查询:
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;
GROUP BY
允许您使用聚合函数,如AVG()
,MAX()
,MIN ,
只是移除重复项目。
SUM()
,COUNT()
$ c> DISTINCT
GROUP BY
lets you use aggregate functions, likeAVG()
,MAX()
,MIN()
,SUM()
,COUNT()
etc whileDISTINCT
just removes duplicates.
这篇关于使用计数获取不同的记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文