Question 89857 in PostgreSQL Question posted in
The official documentation can be found here.

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

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

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search