CODEWITHSUNDEEP
sql
Chris90
Chris90

Reputation: 1998

counting number of occurrences for the same column

I have a table that has a column such as

ID 
ab123
ab321
ab123
ab321
ab321
ab555

Desired Output:

Occurrences_Once | Occurrences_greater_than_one
       1                       2

I want query so I can count number of IDS than occur only once and then more than once

I know I can utilize having, but instead of running 2 queries wanted to know best way to do in one singular query

Thank you

Upvotes: 0

Views: 45

Answers (1)

Tomáš Záluský
Tomáš Záluský

Reputation: 12000

Aggregate rows to compute counts, then aggregate computed counts:

with t(id) as (values
('ab123'),
('ab321'),
('ab123'),
('ab321'),
('ab321'),
('ab555'))

select sum(case when cnt = 1 then 1 end) as Occurrences_Once
     , sum(case when cnt > 1 then 1 end) as Occurrences_greater_than_one
from (
  select id, count(*) as cnt
  from t
  group by id
) x

In Postgres, you can also use count(*) filter (where cnt = 1) instead.

Upvotes: 1

Related Questions