SQL Query, What have I done wrong? I am fairly new to mySQL

Question

Solution problem

SELECT agency_name, Count(*) AS complaint_type_count
FROM service_request_xs
GROUP BY agency_name
ORDER BY Count(*) DESC; 

• solution uploaded

Answer 1

You have to tell the count() function what to count. You can insert an individual column, or * for all of it etc. But you have to count something.

This is a fiddle, showing how it works: https://www.db-fiddle.com/f/dbPnE4BXv8oRRkQY4WQs8v/1

SELECT agency_name, 
       COUNT(DISTINCT compliant_type) AS complaint_type_count
FROM service_request_xs
GROUP BY agency_name
ORDER BY COUNT(DISTINCT compliant_type) DESC;

Answer 2

Usually, when you use the Count() function, you have to add a column name between the parentheses; such as: Count(complaint_type) or else SQL will not know what to count.

You have to do this in the SELECT and the ORDER BY.

You can also use Count(*) to count all lines in your table.