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
Last activity: Jan 28, 2021, 09:59 PM