如何在多列中找到重复计数?

时间:2022-11-17 19:16:03

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.

LAST_COL是主键,因此每次都会有所不同。

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

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

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.

基本上,我的表中有数百万行,我想知道哪一组COL_1,COL_2,COL_3和COL_4具有最多行数。

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.

*我正在使用MS SQL,如果这会有所不同。

4 个解决方案

#1


9  

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

#2


1  

The GROUP BY is what you want here. For example:

GROUP BY就是你想要的。例如:

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*)
FROM my_table
GROUP BY COL_1, COL_2, COL_3, COL_4

#3


1  

If I am understanding correctly all you need is something like:

如果我理解正确你所需要的是:

SELECT COL_1,COL_2,COL_3,COL_4, COUNT(*) AS TOTAL
FROM table
GROUP BY COL_1,COL_2,COL_3,COL_4

#4


1  

are having most number of rows

拥有最多行数

So you want to count, and then ORDER BY the count DESC

所以你想要数,然后ORDER BY计数DESC

SELECT    COL_1, COL_2, COL_3, COL_4, COUNT(*) COUNT_ROWS
FROM      TBL
GROUP BY  COL_1, COL_2, COL_3, COL_4
ORDER BY  COUNT_ROWS DESC

#1


9  

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

#2


1  

The GROUP BY is what you want here. For example:

GROUP BY就是你想要的。例如:

SELECT COL_1, COL_2, COL_3, COL_4, COUNT(*)
FROM my_table
GROUP BY COL_1, COL_2, COL_3, COL_4

#3


1  

If I am understanding correctly all you need is something like:

如果我理解正确你所需要的是:

SELECT COL_1,COL_2,COL_3,COL_4, COUNT(*) AS TOTAL
FROM table
GROUP BY COL_1,COL_2,COL_3,COL_4

#4


1  

are having most number of rows

拥有最多行数

So you want to count, and then ORDER BY the count DESC

所以你想要数,然后ORDER BY计数DESC

SELECT    COL_1, COL_2, COL_3, COL_4, COUNT(*) COUNT_ROWS
FROM      TBL
GROUP BY  COL_1, COL_2, COL_3, COL_4
ORDER BY  COUNT_ROWS DESC