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!

https://stackoverflow.com/a/24017868/1068246 – Muzaffer Galata Jul 19 '20 at 13:28 — Muzaffer Galata, Jul 19 '20 at 13:28

score 2 · Accepted Answer · answered Jul 19 '20 at 13:26

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 '20 at 13:26

GMB

216,147
25
84
135

Thanks you for your help. – mrbipbean Jul 20 '20 at 12:51

Counting without duplicates in Postgresql

1 Answers1