Counting number of occurrences, how to “group by”

querysybase-sql-anywheresyntax

I have the following query, which works as intended. Database is Sybase SQLAnywhere, which follows t-sql syntax for the most part.

I'm trying to add a column to COUNT the number of times each "referred_link" occurs on the table called "cases".

Num_refs is the total number of times a specific "cases.referred_link" exists on the cases table, for each referrer.

Expected output:

casenum:    casetype:    date_opened:    client:    referrer:    num_refs:
30293       MVA           1/1/2021       Joe Smith   Maggie G      3
10293       SF            1/4/2020       Ben R       Simon A       4

Here's the query:

select casenum, 
matcode as casetype, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'repeat client' as reason 
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff' 
and party.our_client='Y' 
and date_opened>='##STARTDATE##' 
and date_opened<='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN 
  (select 
    party_id 
    from 
      (select 
        count(casenum) as casecount, 
        party_id 
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff' 
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
UNION ALL
select casenum, 
matcode, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer, 
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'personal referrer' as reason  
from cases 
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id 
where names.person='Y' 
and date_opened>='##STARTDATE##' 
and date_opened<='##ENDDATE##' 
and party.our_client='Y' 
and party.role='Plaintiff' 
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null) 
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
order by date_opened asc

And here's how I'm trying to do it, entered after line 5:

(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened>='##STARTDATE##' 
  and date_opened<='##ENDDATE##'
) as num_refs,

but I'm getting an error "Function or column reference to 'casenum' must also appear in a GROUP BY"

Can anybody help me straighten this out?

Best Answer

Well, I was able to get it working.

If anyone else has some similar scenario, here's what worked:

select casenum, 
matcode as casetype, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer,
(select
  count(sp_name(referred_link,1))
  from cases
  where sp_name(referred_link,1) = referrer
  and date_opened>='##STARTDATE##' 
  and date_opened<='##ENDDATE##'
  group by cases.referred_link
) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'repeat client' as reason 
from cases
inner join party on cases.casenum=party.case_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on party.party_id=a.names_id
where party.role='Plaintiff' 
and party.our_client='Y' 
and date_opened>='##STARTDATE##' 
and date_opened<='##ENDDATE##'
and cases.case_date_6 is null
and party.party_id IN 
  (select 
    party_id 
    from 
      (select 
        count(casenum) as casecount, 
        party_id 
        from cases
        inner join party on cases.casenum=party.case_id
        where party.role='Plaintiff' 
        and party.our_client='Y'
        and matcode IN ('PMV','MVA','IMV','GPI','IGP','S%F','ISF')
        group by party_id) multicases
        where multicases.casecount>=2)
group by cases.referred_link, cases.casenum, cases.matcode, date_opened, mailing_list
UNION ALL
select casenum, 
matcode, 
date_opened, 
sp_first_party(casenum) as client, 
sp_name(referred_link,1) as referrer, 
count(sp_name(referred_link,1)) as num_refs,
(case when mailing_list is null then 'No' else 'Yes' end) as old_mattar_star, 
'personal referrer' as reason  
from cases 
inner join names on cases.referred_link=names.names_id
left join (select * from mailing_list_defined where mailing_list='Mattar Stars') a on names.names_id=a.names_id
left join provider on names.names_id=provider.name_id
left join party on cases.casenum=party.case_id 
where names.person='Y' 
and date_opened>='##STARTDATE##' 
and date_opened<='##ENDDATE##' 
and party.our_client='Y' 
and party.role='Plaintiff' 
and party.party_id != referred_link
and ((code not like '%Attorney%' and code not like '%Established%' and code not like '%Doctor%' and code not like '%Chiropractor%') or provider.code is null) 
and matcode in ('PMV','IMV','MVA','S%F', 'ISF', 'GPI', 'IGP')
group by cases.referred_link, casenum, cases.matcode, date_opened, mailing_list
order by num_refs desc, date_opened asc
Related Question