I'm trying add a count column to my table. I want to remove duplicate rows and add an additional column with the count of rows removed.

My Table looks like this:

start             institution_code  year
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUBLA           2004
Indianapolis, IN    IUINA           2004
Bloomington,  IN    IUINA           2002
Bloomington,  IN    IUINA           2002

And I'd like the final version to look like this:

start             institution_code  year    Count
Indianapolis, IN    IUBLA           2004    2
Indianapolis, IN    IUINA           2004    1
Bloomington,  IN    IUINA           2002    2

I thought come combination of Count and Group By or even Over but I haven't been able to come up with a working example.

 PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

It should be a pretty straight forward group by and count:

select start, institution_code  , year, count(*) as Count
from yourtable
group by start, institution_code  , year;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.