Count number of items per ID

I have a table as below

INPUT

ID    ITEM
1     Apple
1     Banana
1     Orange
2     Pear
3     Apple
3     Pear

I want to count the items per id, such as

OUTPUT

ID    ITEM    ITEMS_PER_ID
1     Apple    3
1     Banana   3
1     Orange   3
2     Pear     1
3     Apple    2
3     Pear     2

Using count with group by does not achieve the desire result. What would be the best way to do this?

Answer

Use COUNT():

select *, count(item) over(partition by id) as items_per_id from t  

Result:

id  item    items_per_id
--  ------  ------------
1   Apple              3
1   Banana             3
1   Orange             3
2   Pear               1
3   Apple              2
3   Pear               2

For reference, the data script I used is:

create table t (
  id int,
  item varchar(10)
);

insert into t (id, item) values 
  (1, 'Apple'),
  (1, 'Banana'),
  (1, 'Orange'),
  (2, 'Pear'),
  (3, 'Apple'),
  (3, 'Pear');