Number of similar columns occurrences

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

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

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?

Answer

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