COUNT(*) function not working in SELECT query

Question

I wrote a simple SQL statement that counts the number of category IDs in a table. Problem is, its returning NULL.

$db = JFactory::getDbo();
$query = $db->getQuery(true);
$query->select($db->quoteName(array('category_id','COUNT(*)')));
$query->from($db->quoteName('#__eb_event_categories'))
      ->group($db->quoteName('category_id'));
$db->setQuery($query);
$results = $db->loadObjectList(); 
var_dump($results);

When I remove COUNT(*) the select statement is working normally. What did I write incorrectly?

Answer 1

You need to remove quoteName from your select clause, so it will then look like this:

$query->select(array('category_id','COUNT(*)'));

Using $db->quoteName() on an array will wrap all array values in backticks when the query is rendered.

category_id is okay to wrap in backticks via $db->quoteName() because it is a column name.

COUNT(*) is a MySQL function. It loses its meaning if wrapped in backticks.

Your original query was seeking a column called COUNT(*) -- which of course isn't correct (or available).