I have in POSTGRE a table called MEMBER with 3 columns like this:

I need to count on how many bands each member is/was member of. Is there a way to count this?

I tried the next code:

SELECT DISTINCT e.id_musician,count(id_band)
FROM MEMBER e
GROUP BY e.id_musician, e.instrument
ORDER BY e.id_musician;

But it gives me this result:

I want to get how many bands for each member for example

but with out the double row on musician 2

Any suggestions?

CodePudding user response：

You can use count(distinct id_band):

select id_musician, count(distinct id_band) from members group by id_musician;