I'm trying get the MAX value of my subquery denoted as NUM_of_CUST column and to display it alongside with the artist name in tge main query so it would display like this:
ARTIST_NAME | NUM_of_CUST
Select A.Lastname ||''|| A.FirstName as Artist_Name from dhey.ARTIST A
LEFT JOIN
(SELECT * FROM(
SELECT COUNT(*) As Num_of_Customer From Dhey.CUSTOMER_ARTIST_INT CAI
GROUP BY ARTISTID
ORDER BY COUNT(*) desc) WHERE ROWNUM = 1) B on A.ArtistID = B.ArtistID;
However SQL is throwing me B"."ARTISTID": invalid identifier exception.
Can anyone advise on this?
Thank you!