Calculate number of distinct instances of value in column

Question

long time lurker. I've searched and searched though none of the solutions work for me. I'm working in a Sybase (ASE) db (most mssql/mysql transactional db solutions will work just fine)

In my example, I'm trying to calculate/count the number of times a specific 'party_id' is listed in a column. The problem I'm having is that it's only counting FOR each row- so of course the count is always going to be 1.

See output: (I would like for party_id 130568 to show '2' in the refs column, 125555 to show '5', etc.) output

Here is my query:

select
count(distinct p.party_id) as refs,
p.party_id,
sp_first_party(casenum),
c.casenum, 
mld.mailing_list,
p.our_client
from cases c
inner join party p on c.casenum=p.case_id
inner join names n on n.names_id=p.party_id
inner join mailing_list_defined mld on n.names_id=mld.names_id
where 
mld.mailing_list like 'Mattar Stars'
and mld.addr_type like 'Home'
and n.deceased='N'
and p.our_client='Y'
group by p.party_id, c.casenum, mld.mailing_list, p.our_client
order by sp_first_party(casenum) asc

Any tips would be greatly appreciated. Thank you

Answer 1

Sounds like you need to be using an APPLY statement. Not sure if the join criteria on the APPLY statement is correct, but you should be able to extrapolate the logic. See if that will work with Sybase.

SELECT  pic.PartyInstanceCount AS refs
        ,p.party_id
        ,sp_first_party(casenum)
        ,c.casenum
        ,mld.mailing_list
        ,p.our_client
FROM    cases AS c
        INNER JOIN party AS p ON c.casenum = p.case_id
        INNER JOIN names AS n ON n.names_id = p.party_id
        INNER JOIN mailing_list_defined AS mld ON n.names_id = mld.names_id
        OUTER APPLY (
                        SELECT  COUNT(1) AS PartyInstanceCount
                        FROM    party p2
                        WHERE   p2.case_id = c.casenum
                    ) pic
WHERE   mld.mailing_list LIKE 'Mattar Stars'
        AND mld.addr_type LIKE 'Home'
        AND n.deceased = 'N'
        AND p.our_client = 'Y'
ORDER BY
        sp_first_party(casenum) ASC