COUNT BY GROUP in DB Read

Question

Hello again,

In my DB Read action I need the rather simple query

SELECT lv,COUNT(*)  
FROM mytable        
GROUP BY lv;

First I tried [attachment=0]cf-Count-by-group-01.png[/attachment], which resulted in [attachment=1]cf-Count-by-group-02.png[/attachment], where only the lv column made it into the data array.

However, debugger showed that the query string looked nice and correct: [attachment=2]cf-Count-by-group-03.png[/attachment].
After copying it into phpMyAdmin, there it worked.
[attachment=3]cf-Count-by-group-04.png[/attachment]

I presumed the '*' (asterisk) causes the problem. Trying to bypass it by adding "lv, COUNT(*) AS sometext" into the 'Fields' input of DB Read made the action completely crash. Now I run out of ideas how to get my query done.

Anyone here, who might help me?
Thanks and regards,
Herbert

GreyHead · Accepted Answer

HI Herbert,

The simplest answer is to use a Custom Code action instead

$db = \JFactory::getDBO();
$query = "
    SELECT `lv`, COUNT(*) AS count
      FROM `#__mytable`        
      GROUP BY `lv`
      ORDER BY `lv`;
";
$db->setQuery($query);
$form->data['lv'] = $db->loadAssoctList();
?>

Not tested and probably needs tweaking!

It might also work if you used say COUNT(`lv`) AS count

Bob