CODEWITHSUNDEEP
sqlpostgresqlgroup-bycountsubquery
Charles De Labra
Charles De Labra

Reputation: 15

Is there a way to count combination of columns in PostgreSQL?

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

id_musician id_band instrument
1 1 Guitar
1 1 Vocals
2 3 Vocals
2 4 Vocals
2 4 Guitar
3 1 Guitar

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:

id_musician count
1 1
2 1
2 2
3 1

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

id_musician count
1 1
2 2
3 1

but with out the double row on musician 2

Any suggestions?

Upvotes: 1

Views: 371

Answers (1)

Ajax1234
Ajax1234

Reputation: 71451

You can use count(distinct id_band):

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

Upvotes: 2

Related Questions