Find total count of HIT and MISS using ID and skipping duplicate based on another row in POSTGRES-CodePudding

I have a table like-

    id---------rowNum------status
    aa23       1            HIT
    aa23       1            HIT
    aa23       1            HIT
    aa23       2            MISS
    aa23       2            MISS
    aa24       2            MISS
    aa24       2            MISS

So basically I need to get the count of and HIT and MISS given the ID

For example- if I am given aa23 I should return

    status-----count
    HIT          1
    MISS         1

The rowNums should be distinct so given id=aa23 the HIT occurs thrice but all are rowNum 1 so should be counted once same for Miss

CodePudding user response：

You can do it using group by as follows :

with cte as (
  select id, rowNum, status
  from mytable
  where id = 'aa23'
  group by id, rowNum, status
)
select status, count(1) 
from cte
group by status;

Demo here

CodePudding user response：

you could elemente all duplicates first and also only select the id you want and the count the resulting rows

create table mytable (
  id varchar(10),
  rowNum int,
  status varchar(10)
);

insert into mytable values
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,1 ,'HIT'),
('aa23' ,2 ,'MISS'),
('aa23' ,2 ,'MISS'),
('aa24' ,2 ,'MISS'),
('aa24' ,2 ,'MISS');

CREATE TABLE

INSERT 0 7

WITH CTE AS 
( SELECT DISTINCT id,rowNum,status
  FROM mytable)
SELECT id, status, COUNT(*) as count_
FROM CTE
GROUP BY id, status

id	status	count_
aa24	MISS	1
aa23	HIT	1
aa23	MISS	1

SELECT 3

fiddle