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

You should learn to use modern, explicit, **standard** `JOIN` syntax. — Gordon Linoff, Dec 11 '19 at 17:56
Could you point me in a proper direction if I were to use joins to do this? — boog, Dec 11 '19 at 18:00
By the way, I'm building a report for an CMS application that does not allow joins in it's report building syntax. However, I'm thinking if I could make it work in a normal query I could then figure out a way to translate it. — boog, Dec 11 '19 at 18:35
https://stackoverflow.com/questions/1018822/inner-join-on-vs-where-clause or https://blog.jooq.org/2019/04/09/the-difference-between-sqls-join-on-clause-and-the-where-clause/ — Jeremy, Dec 11 '19 at 19:04
I modified the query to do the same thing with joins... looking into that link to see if there might be something going totally over my head here... — boog, Dec 11 '19 at 19:50

score 0 · Accepted Answer · answered Dec 11 '19 at 19:56

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

That does run without error, and while the results in the refs column are a little wonky, you have set me down the path I think I need to take! thank you! — boog, Dec 11 '19 at 21:17
Not sure how this gets voted as the answer since 'outer apply' is not supported in Sybase ASE ... — markp-fuso, Dec 11 '19 at 21:54
Not sure how it's not supported, it runs just fine (as well as CROSS APPLY)... and I am indeed using sybase... although it's not calculating how I would like it to- it is evaluating for each row, which was the functionality I was looking for — boog, Dec 12 '19 at 21:25

Calculate number of distinct instances of value in column

1 Answers1