Search code examples
sqlcountresultset

Count column in result set

I have a result set like below enter image description here

what I need to do is count the times the acct_nmbr is in the result set to get something like this. The end goal is to use a having clause to get rid of any account that appears more than once.

enter image description here

I have used:

select distinct Obj_id, acct_nmbr as Acct_nmbrr, datee ,count (acct_nmbrr) as "count"

but it is not returning the right results. I want to count only the result set.

Solution

  • My idea is using row number

    SELECT ObjID, AcctNmbr, Date, 
ROW_NUMBER() OVER (PARTITION BY AcctNmbr ORDER BY ObjID) 
FROM sometable

    And then remove the rows with row_number > 1, according to your logic.