SQL query to create a list and more

Question

Below is a table with the entered data.

CREATE TABLE police(event_id VARCHAR(100), search_type VARCHAR(1000),
                    reported_by VARCHAR(100));
INSERT INTO police VALUES ('gng67g6yf', 'person','ceridigion' );
INSERT INTO police VALUES ('bfewbqfjhfb', 'person', 'ceridigion' );
INSERT INTO police VALUES ('ytftdfctg', 'n/a','ceridigion'  );
INSERT INTO police VALUES ('yufvugyu', 'person','pembrokeshire',  );

What I'm trying to figure out is a query in which the report_by message will be displayed, as well as search queries that have occurred in areas. Something like this below:

reported_by     Stopped and searched
ceridigion              2
pembrokeshire           1   

Answer 1

SELECT reported_by,
       SUM(CASE WHEN search_type = 'person' THEN 1 ELSE 0 END) AS `Stopped and searched`
FROM police
GROUP BY reported_by

Demo here:

SQLFiddle

Answer 2

If I understand correctly, you just need to count the number of rows (for reported_bywhich have search_type, which is not n/a:

SELECT   reported_by, COUNT(*) AS "Stopped and searched"
FROM     police
WHERE    search_type != 'n/a'
GROUP BY reported_by