CODEWITHSUNDEEP
sqlpostgresql
saintlyzero
saintlyzero

Reputation: 1852

SQL count number of records where value remains constant

I need to find the count of tracker_id where position remains 1 through out the table.

tracker_id | position 
---------------------
 5         |     1
 11        |     1
 4         |     1
 4         |     2
 5         |     2
 4         |     1
 4         |     1
 11        |     1
 14        |     1
 9         |     2

Here, the output should be 2 since, position of tracker_id:11 and 14 remains 1 through out the table.

Upvotes: 0

Views: 116

Answers (5)

Rajat
Rajat

Reputation: 5803

Using uncorrelated subquery

select count(distinct tracker_id)
from t
where position=1
and tracker_id not in (select tracker_id from t where position<>1);

Using window function

select count(distinct tracker_id)
from (select *, avg(position) over (partition by tracker_id) as avg_pos from t) a
where avg_pos=1;

This one is just for giggles

select distinct count(*) over () 
from t
group by tracker_id 
having count(*) = sum(position);

And if you really want to have fun

select count(distinct tracker_id)-count(distinct case when position<>1 then tracker_id end)
from t;

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270713

You can also do:

select ( count(distinct tracker_id) - 
         count(distinct tracker_id) filter (where position <> 1)
       ) as num_all_1s
from t;

Upvotes: 2

red
red

Reputation: 25

declare @table1 as table (tracker_id int,postion int)

insert into @table1 values (5,1)
insert into @table1 values (11,1)
insert into @table1 values (4,1)
insert into @table1 values (4,2)
insert into @table1 values (5,2)
insert into @table1 values (4,1)
insert into @table1 values (4,1)
insert into @table1 values (11,1)
insert into @table1 values (14,1)
insert into @table1 values (9,2)

select count(tracker_id),tracker_id,postion from @table1 group by tracker_id,postion

Upvotes: 2

digital.aaron
digital.aaron

Reputation: 5707

If position can only be 1, then you can use this, which gets all the tracker_ids with only a single position value, and then limits that to those records where position = 1:

WITH agg AS
(
    SELECT 
        tracker_id 
        , p = MAX(position)
    FROM table1
    GROUP BY tracker_id
    HAVING COUNT(DISTINCT position) = 1
)
SELECT COUNT(tracker_id) 
FROM agg 
WHERE p = 1

Upvotes: 1

zip
zip

Reputation: 4061

You can use not exists

select count(*) from tbl a
where not exists(select 1 
                 from tbl b 
                 where a.tracker_id = b.tracker_id 
                 and a.position  <> b.position )
and a.position = 1

Output: 2

Upvotes: 2

Related Questions