ChaosConscious ChaosConscious - 3 months ago 5
SQL Question

SQL query to make a list and more

Below is a table with data inserted.

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 am trying to find out is a query which will list reported_by and have the searches that took place in the areas aswell. Something like this below:

reported_by Stopped and searched
ceridigion 2
pembrokeshire 1

Answer
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

Comments