Thread

Is SELECT COUNT(foo) supported in the query module?

This is a valid query which returns a few hundred rows:

SELECT c.cat_id, c.cat_name, e.entry_id FROM exp_gallery_categories AS c JOIN exp_gallery_entries AS e ON c.cat_id = e.cat_id WHERE e.gallery_id='4'

But this returns nothing:

SELECT c.cat_id, c.cat_name, COUNT(e.entry_id) FROM exp_gallery_categories AS c JOIN exp_gallery_entries AS e ON c.cat_id = e.cat_id WHERE e.gallery_id='4'

Moved to HowTo by Moderator

Make sure you query as such is valid by testing in the control panel (or phpMyAdmin or similar). SELECT COUNT is perfectly possibly with the query module.

right, that’s what i’m saying.

the 1st query works in the control panel utility, the second one returns no results (not a mySQL error)

I am slightly confused: are we talking about the query module, or the control panel? If the latter, do you mean you are not getting any error message?

Testing the query in the CP before working with it in a template, I find that when I merely SELECT e.entry_id, I get several hundred results (as expected).

When I try to SELECT COUNT(e.entry_id) I get no results.

Try something like this:

select count(entry_id) as cnt

That works on

SELECT COUNT(entry_id) AS cnt FROM exp_gallery_entries WHERE gallery_id='4'

but not the joined version (which I need to group by cat_id)

SELECT c.cat_id, c.cat_name, COUNT(e.entry_id)AS cnt FROM exp_gallery_categories AS c JOIN exp_gallery_entries AS e ON c.cat_id = e.cat_id WHERE e.gallery_id='4'

Guess I need to do some mySQL woodshedding

Probably. Moving to HowTo for additional input from the community.

If I try to run that query, I receive the following error:

MySQL ERROR:

Error Number: 1140

Description: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause

Query: SELECT SQL_CALC_FOUND_ROWS c.cat_id, c.cat_name, COUNT(e.entry_id)AS cnt FROM exp_gallery_categories AS c JOIN exp_gallery_entries AS e ON c.cat_id = e.cat_id WHERE e.gallery_id='4' LIMIT 0, 100

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html

So a little tweak, and it should work:

SELECT c.cat_id, c.cat_name, COUNT(e.entry_id) AS count
FROM exp_gallery_categories c
JOIN exp_gallery_entries e
ON c.cat_id = e.cat_id
WHERE e.gallery_id='4'
GROUP BY c.cat_id;

Hope that helps.

-greg

Yes!

GROUP BY was the issue.

Thank you, all…