I have a table as below
INPUT
x
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?
Advertisement
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');