发生类似列的数量 [英] Number of similar columns occurences

问题描述

我是新的SQL SERVER，需要你的建议。
我有一个大表与商店信息。以下是bench_id列：

I am new to SQL SERVER and need your advise on something. I have a big table with stores information. Here is bench_id column:

**bench_id**
31
51
51
61
61
61
71
71

我已创建了另一个包含两列的小表：

I have created another small table with two columns:

**distinct_bench**           **number**
-----------------------------

31                               1
51                               2
61                               3
71                               2

第二个表的NUMBER列显示了bench_id的出现次数。
我尝试过这个代码：

The second table's NUMBER column is showing the number of bench_id's occurrences. I have tried this code:

insert into [dbo].BATCH_ID (batch_id, number)
select 
     distinct [dbo].big_table.batch_id,
           (select count([dbo].big_table.batch_id) from [dbo].big_table)
     from [dbo].big_table

但是无法得到正确的答案，我期待与bench_id的发生数。
你能指出我做错了什么吗？

But can not get the right answer I am expecting with number of bench_id occurrences. Can you point out what i am doing wrong? thank you in advance!

推荐答案

您需要拥有 GROUP BY 因为您需要使用 COUNT（）来计算 benchID 的发生。

You need to have GROUP BY clause since you need to use COUNT() to count the occurence of benchID.

INSERT INTO BATCH_ID (distinct_bench), number)
SELECT  benchID as distinct_bench, COUNT(*) number
FROM    big_table
GROUP   BY BATCH_ID 

这篇关于发生类似列的数量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！