Counting without duplicates in Postgresql

Question

I have a table such as the following

**SP     MA     SL   NG**
jame     j001   1    20200715
jame     j001   -1   20200715
jame     j001   1    20200715
pink     p002   12   20200720 
pink     p002   -3   20200720 
pink     p002   12   20200721

I'm trying to count record but I have to eliminate duplicates. So, I want a result like

 **SP    count
jame     1
pink     2

Any suggestions?I could do with some help, please. Thanks you!

stackoverflow.com/a/24017868/1068246

Muzaffer Galata
– Muzaffer Galata

2020-07-19 13:28:06 +00:00
Commented Jul 19, 2020 at 13:28 — Muzaffer Galata
– Muzaffer Galata, Commented Jul 19, 2020 at 13:28

GMB · Accepted Answer · 2020-07-19 13:26:18Z

2

Are you looking for count(distinct ...)?

For example, if you want the count of distinct values in column ng for each sp:

select sp, count(distinct ng) cnt from mytable group by sp

If you want to eliminate records that are complete duplicates, another option is select distinct in a subquery first:

select sp, count(*) cnt
from (select distinct sp, ma, sl, ng from mytable) t
group by sp

answered Jul 19, 2020 at 13:26

GMB

224k25 gold badges103 silver badges151 bronze badges

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

1 Comment

mrbipbean Over a year ago

Thanks you for your help.

Collectives™ on Stack Overflow

Counting without duplicates in Postgresql

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related