如何在几列之间找到重复计数？ [英] How to find duplicate count among several columns?

问题描述

这里是一个模仿我的情景的范例表格：

  COL_1 COL_2 COL_3 COL_4 LAST_COL 
 APX NY 10 
 APX NY 11 
 APY NY 12 
 APY NY 13 
 APX NY 14 
 BQX NY 15 
 BQY NY 16 
 BQY CA 17 
 BQY CA 18 
  

LAST_COL是主键，因此每次都会不同。 p>

我想忽略LAST_COL并收集与4列其余部分相关的统计信息。

基本上，我的表中有数百万行，我想知道 COL_1，COL_2，COL_3和COL_4 中的行数最多。

因此，我想要一个查询，可以输出所有唯一的行及其出现次数。

  COL_1 COL_2 COL_3 COL_4 TOTAL 
 APX NY 3 
 APY NY 2 
 BQX NY 1 
 BQY NY 1 
 BQY CA 2 
  

感谢任何人帮助我。

解决方案

 我使用MS SQL， SELECT COL_1，COL_2，COL_3，COL_4，COUNT（*）
 FROM MyTable 
 GROUP BY COL_1，COL_2，COL_3，COL_4 
   
 
如果你想要除去没有重复的行：
  SELECT COL_1，COL_2，COL_3，COL_4，COUNT（*）
 FROM MyTable 
 GROUP BY COL_1，COL_2，COL_3，COL_4 
 HAVING COUNT（*）& 1 
  
 
Here is a sample table that mimics my scenario:
COL_1   COL_2   COL_3   COL_4   LAST_COL
A       P       X       NY      10
A       P       X       NY      11
A       P       Y       NY      12
A       P       Y       NY      13
A       P       X       NY      14
B       Q       X       NY      15
B       Q       Y       NY      16
B       Q       Y       CA      17
B       Q       Y       CA      18
The LAST_COL is a primary key so it will be different every time.

I want to ignore LAST_COL and gather some statistics related to the rest of the 4 columns.

Basically, I have millions of rows in my table, and I want to know which set of COL_1, COL_2, COL_3 and COL_4 are having most number of rows. 

So, I want a query which can output me all the unique rows with their count of occurrences.
COL_1   COL_2   COL_3   COL_4   TOTAL
A       P       X       NY      3
A       P       Y       NY      2
B       Q       X       NY      1
B       Q       Y       NY      1
B       Q       Y       CA      2
Thanks to anyone who helps me with this.

*I am using MS SQL, if that would make any difference.
解决方案
SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*)
FROM MyTable
GROUP BY COL_1, COL_2, COL_3, COL_4
If you ever want to weed out rows that don't have a duplicate:
SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*)
FROM MyTable
GROUP BY COL_1, COL_2, COL_3, COL_4
HAVING COUNT(*) > 1


                        
这篇关于如何在几列之间找到重复计数？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！