CODEWITHSUNDEEP
sqloracle-database
ABB25
ABB25

Reputation: 101

Stuck with linking SQL queries together

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!

Upvotes: 2

Views: 78

Answers (2)

ABB25
ABB25

Reputation: 101

I have solved my answer with this 

SELECT A.lastname ||''||A.firstname as Artist_Name, COUNT(CAI.ARTISTID) AS NumberOfCustomers
FROM dhey.CUSTOMER_ARTIST_INT CAI
LEFT JOIN dhey.CUSTOMER C
ON CAI.CUSTOMERID = C.CUSTOMERID
LEFT JOIN dhey.ARTIST A
ON CAI.ARTISTID = A.ARTISTID
group by  A.lastname,  A.firstname
having count(*) = (select max(count(ArtistID))
                     from dhey.CUSTOMER_ARTIST_INT
                    group by ArtistID);

Thank you all that helped!

Upvotes: 0

Steven
Steven

Reputation: 15258

I do not really understand what you want to do but i think it is either you want the number of customers per artist

Select  A.Lastname ||''|| A.FirstName as Artist_Name 
        COUNT(*) as nb_cust
from dhey.ARTIST A 
LEFT outer JOIN Dhey.CUSTOMER_ARTIST_INT B on A.ArtistID = B.ArtistID
group by A.ArtistID, A.Lastname ||''|| A.FirstName
order by nb_cust desc

Or you want the max number of customer overall and link that to your artist... which is a cross product with one line. 

Select  A.Lastname ||''|| A.FirstName as Artist_Name 
        nb_cust_max
from dhey.ARTIST A 
cross join  (   select max(nb_cust) as nb_cust_max
                from    (   select count(*) as nb_cust 
                            From Dhey.CUSTOMER_ARTIST_INT 
                            group by ARTISTID
                        )
            )

Am I close ? or totally wrong?

Otherwise, as suggested by MatBailie, you can just add the missing field

Select A.Lastname ||''|| A.FirstName as Artist_Name from dhey.ARTIST A 
LEFT JOIN
(SELECT * FROM(
SELECT ARTISTID, 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;

Upvotes: 2

Related Questions