Missing expression inside sub-query statement?

Question

I am using the infamous "CityJail" schema to answer a question "List the names of all criminals who have committed more than average number of crimes and aren’t listed as violent offenders."

Here is my code:

SELECT first, last 
FROM criminals NATURAL JOIN
crimes
GROUP BY first, last
HAVING COUNT(*) > (SELECT AVG(COUNT(DISTINCT crime_id))
FROM crimes)
AND (SELECT v_status = 'N' FROM crimes)
GROUP BY first, last
);

but I get an error:

ORA-00936: missing expression 00936. 00000 - "missing expression" *Cause:
*Action: Error at Line: 7 Column: 22

When I change my code to:

SELECT first, last 
FROM criminals NATURAL JOIN
crimes
GROUP BY first, last
HAVING COUNT(*) > (SELECT AVG(COUNT(DISTINCT crime_id))
FROM crimes)
AND v_status = 'N'
GROUP BY first, last
);

I get an error:

ORA-01787: only one clause allowed per query block 01787. 00000 - "only one clause allowed per query block" *Cause:
*Action: Error at Line: 8 Column: 1

What am I doing wrong?

criminals:

Name        Null?    Type         
----------- -------- ------------ 
CRIMINAL_ID NOT NULL NUMBER(6)    
LAST                 VARCHAR2(15) 
FIRST                VARCHAR2(10) 
STREET               VARCHAR2(30) 
CITY                 VARCHAR2(20) 
STATE                CHAR(2)      
ZIP                  CHAR(5)      
PHONE                CHAR(10)     
V_STATUS             CHAR(1)      
P_STATUS             CHAR(1)    

crimes:

Name            Null?    Type      
--------------- -------- --------- 
CRIME_ID        NOT NULL NUMBER(9) 
CRIMINAL_ID     NOT NULL NUMBER(6) 
CLASSIFICATION           CHAR(1)   
DATE_CHARGED             DATE      
STATUS                   CHAR(2)   
HEARING_DATE             DATE      
APPEAL_CUT_DATE          DATE      
DATE_RECORDED            DATE      

Answer 1

Fix Your Scalar Subquery in the Having Clause

  SELECT first
      , last
   FROM criminals cls
NATURAL
   JOIN crimes 
  WHERE v_status = 'N'
  GROUP BY first
      , last
 HAVING COUNT(1) >(
         SELECT AVG(COUNT(DISTINCT crime_id))
   FROM crimes
   GROUP BY criminal_id);
  

The scalar query is malformed in both of your attempts.

I moved the condition WHERE v_status = 'N' to the main query (you are only concerned about criminal whom are non-violent).