Sample Header Ad - 728x90

Counting number of occurrences, how to "group by"

0 votes
1 answer
71 views
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=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='##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?
Asked by boog (153 rep)
Jan 28, 2021, 09:01 PM
Last activity: Jan 28, 2021, 09:59 PM