How is my SQL subquery evaluating to more than one row? When run independently, it works fine, but d-CodePudding

I am obtaining values from two tables. When I run the subquery in its own PROC SQL statement in SAS, it runs fine, with the count of citations for each ID. When I input the subquery into my SELECT outer query, it gives me ERROR: Subquery evaluated to more than one row. I am having a hard time determining the cause of this issue.

The subquery should result in one row of count of citations per ID. I am trying to get the count of citations (per ID) into my outer query. Not all items from B will be in A (hence the left join on B).

SELECT 
    A.AREA
    ,A.NAME
    ,B.ID
    ,(
        SELECT 
            COUNT(B.TYPE) 
        FROM 
            EVAL.CITATIONS AS B
        GROUP BY 
            B.ID
        ) 
        AS COUNT_CITATIONS

FROM 
    EVAL.OCT AS A 

LEFT JOIN EVAL.CITATIONS 
    ON A.DBA = B.NAME

ORDER BY A.NAME ASC

;

I expected the outer query to pull the counts for the citations per ID. The citations are coming from table B (which I'm using to left join into table A). I have been searching forums for this error and I understand that my query is resulting in more than one row, but I can't figure out why the outer query is not simply pulling the counts I need from ID when the left join completes.

I also tried adding in the subquery this WHERE clause after researching some similar questions to no avail.

WHERE FACID = CDPH_CITATIONS.FACID

CodePudding user response：

You need to use a correlated subquery where your subquery references your main query e.g.

SELECT 
A.AREA
,A.NAME
,B.ID
,(
    SELECT 
        COUNT(C.TYPE) 
    FROM 
        EVAL.CITATIONS AS C
    WHERE B.ID = C.ID
    GROUP BY 
        C.ID
    ) 
    AS COUNT_CITATIONS

FROM 
EVAL.OCT AS A 

LEFT JOIN EVAL.CITATIONS 
ON A.DBA = B.NAME

ORDER BY A.NAME ASC

However, I don’t think you need a subquery at all, you can just count the B records and group by the other columns in your main query