select count(field)

Hi averybody
probably it is a stupid question, but.....
I want to create in CC a sql statement like this

select a, b, count(b) as num from table group by a, b

How can I do this?
I tried to put "count(b) as num" in Model/Fields, but in this way it generate a wrong sql code.

Thanks
Monica

Hi Monica,

Please try this:

:count(b) as num

But I'm not sure if this would make the count usable, so you may also try:

:count(b) as `Model.num`

Regards,
Max

Hi Max, I tried the same as you adviced to Monica, but unfortunately it doesn't work :?
Specificly Joomla reports this kind of error (maybe it will help you):

... SELECT COUNT(Article.id) as `Article.num` AS `COUNT(Article.id) as `Article.num` FROM ...

Please, do you have some other idea?

Thank you

Hi aandree,

Please try this in the "fields" box:

<?php
return array("COUNT(Article.id)" => "Article.num");

Regards,
Max

Hi Max,
thank you for your suggestion. When I run your code, the COUNT part of the expression is db quoted in a weird way, and obviously does not work:

SELECT `COUNT(Article`.`id)` AS `Article.num` FROM ...

However, when I omit the model prefix like this (not very robust, but possible in my case):

return array("COUNT(id)" => "Article.num");

it produces the expected SQL

SELECT COUNT(`Article`.`id`) AS `Article.num` FROM …

Don’t know what the problem is, but thought this may be interesting for you, and maybe helpful to someone else🙂
Thank you for your quick response and great support, we greatly appreciate it.

Thanks for the info, but do you have the latest update ? I think the latest update should parse the aliased field name inside the function correctly.

Regards,
Max

Yes, I have the latest 5.0.2 version.