CODEWITHSUNDEEP
sql
codeRed
codeRed

Reputation: 53

SQL invalid identifier in WHERE clause

My database diagram...

https://s13.postimg.org/4rqz9purr/image.jpg

I need to list the name of each officer who has reported more than the average number of crimes officers have reported.

Here is my SQL...

SELECT 
    last, first
FROM 
    officers 
JOIN 
    crime_officers USING (officer_id) 
JOIN 
    crimes USING (crime_id)
JOIN 
    crime_charges USING (crime_id)
WHERE 
    crime_charges > ALL (SELECT AVG(COUNT(*)) FROM crime_charges);

However, I am getting this error:

invalid identifier on line 6 "crime_charges"

Upvotes: 0

Views: 1832

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

Your query seems off. To get the average number of crimes per officer (that has reported a crime):

SELECT COUNT(*) / COUNT(DISTINCT officer_id) 
FROM crime_officers ;

To get the officers that have more than the average, use a HAVING clause:

select co.officer_id
from crime_officers co
group by co.officer_id
having count(*) > (select count(*) / count(distinct officer_id)
                   from crime_officers
                  );

You can use additional JOINs to bring in additional columns.

EDIT:

It is not so hard to get the officers's names:

select co.officer_id, o.last, o.first
from crime_officers co join
     officers o
     on co.officer_id = o.officer_id
group by co.officer_id, o.last, o.first
having count(*) > (select count(*) / count(distinct officer_id)
                   from crime_officers
                  );

Upvotes: 2

Little Santi
Little Santi

Reputation: 8783

The cause of this error is that, right into the WHERE clause, you used a table name (crime_charges) instead of a single expression. You should replace it by an expression which computes the number of crimes reported by an officer:

WHERE number_of_crimes_reported_by_an_officer >ALL (SELECT AVG(COUNT(*)) FROM crime_charges) ;

But getting deeper into your problem, you are still far from the right solution. If the requested result is a set of officers, your query should browse directly the officers table (with no other joins), adding the proper conditions in the WHERE clause. Something like this:

SELECT last, first
FROM officers
WHERE (compute_number_of_crimes_reported_by_an_officer) >ALL (SELECT AVG(COUNT(*)) FROM crime_charges) ;

See? In this way, you'll get a list of officers. Instead, if you add joins to related tables, the number of records will be unnecessary multiplied.

Upvotes: 2

Related Questions