CODEWITHSUNDEEP
sqlpostgresql
sm1994
sm1994

Reputation: 355

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?

Upvotes: 0

Views: 938

Answers (2)

The Impaler
The Impaler

Reputation: 48850

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');

Upvotes: 2

zip
zip

Reputation: 4061

Use a subquery to get the totals

select a.ID,    ITEM,    ITEMS_PER_ID

from tbl a
inner join
(
  select ID, count(distinct ITEM) as ITEMS_PER_ID
  from tbl group by ID
)b on a.ID = b.ID

Upvotes: 0

Related Questions