COUNT(*) with GROUP BY does not work as expected

WindBridges

Hello, guys. I need to execute such query: SELECT name, COUNT(*) FROM table GROUP BY name LIMIT 0,10
I do:

$this->table->select('name, COUNT(*)');
$this->table->group('name');
$this->table->page(1,10);
$this->table->fetchPairs(...);

But also I need to count number of rows in while result set without limit. So I insert before fetchPairs:

$this->table->count('*');

I see that ORM generates such queries:

SELECT COUNT(*) FROM table
SELECT name, COUNT(*) FROM table GROUP BY name LIMIT 10 OFFSET 0

But where is GROUP BY in first query? I need count of grouped results, not all of them. How to do exactly that I need?

Last edited by WindBridges (2012-04-02 00:46)

h4kuna

Hi try this

$this->table->select('name, COUNT(*)')->group('name')->page(1,10)->fetchPairs(...);

petr.pavel

Shouldn't count('*') come before page() instead of before fetchPairs()? Just guessing.

Anyway, if it doesn't work, you'll probably have to run the two queries manually – one without page() counting the total # of rows, and one with page() for the per-group counts. For the total count, you can COUNT(DISTINCT name) (without group) or just run your query with group and fetchPairs and then count with PHP function count().