英文:
Find total count of HIT and MISS using ID and skipping duplicate based on another row in POSTGRES
问题
我有一张表如下 -
id---------rowNum------status
aa23 1 HIT
aa23 1 HIT
aa23 1 HIT
aa23 2 MISS
aa23 2 MISS
aa24 2 MISS
aa24 2 MISS
基本上，我需要得到给定ID的HIT和MISS的计数
例如 -
如果我给定aa23，我应该返回
status-----count
HIT 1
MISS 1
rowNum应该是不同的，所以给定id=aa23，HIT出现三次，但都是rowNum 1，所以应该计为一次，Miss也是如此。
英文:
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
答案1
得分: 0
你可以使用group by来实现，如下所示：
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;
英文:
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;
答案2
得分: 0
你可以首先去除所有的重复行，然后只选择你想要的id，并计算结果行的数量。
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');
然后执行以下SQL查询：
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_ |
|---|---|---|
| aa23 | HIT | 1 |
| aa23 | MISS | 1 |
| aa24 | MISS | 1 |
英文:
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');
> status
> CREATE TABLE
>
> status
> 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 |
| > ``` status | ||
| > SELECT 3 | ||
| > ``` |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论