Postgresql how to find distinct values and count

Question

I have a table with ID, data 1, and few other columns... data 1 has NA as well.

I need to find a distinct ID, followed by the total count of each ID and the count for each ID whose data has NA. I need to determine how much of NA values are loaded for each ID.

Thanks Sai

Stefanov.sm · Accepted Answer · 2020-12-14 06:58:50Z

3

select 
  id,
  count(*) as total_count,
  count(*) filter (where data1 = 'NA') as na_count
from _table 
group by id;

answered Dec 14, 2020 at 6:58

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Popeye · Accepted Answer · 2020-12-14 07:04:30Z

1

You can use standard sql with conditional aggregation as follows:

Select id, count(1) as total,
       Count(case when data1='NA' then 1 end) as NA_count
  From your_table
Group by id;

answered Dec 14, 2020 at 7:04

Popeye

36k4 gold badges12 silver badges31 bronze badges

Collectives™ on Stack Overflow

Postgresql how to find distinct values and count

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related