Re: urgent: Trying to get COUNT for fairly elaborate query

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



sneakyimp wrote:


chris smith-9 wrote:
Doing this is actually rather easy.

Replace this:

SELECT e.id, e.title, e.subheading, eta.start_timestamp, eta.end_timestamp, e.zip, e.bold, e.outline, e.color, e.subheading, COUNT(esa.id) AS subcat_count

With:

SELECT COUNT(e.id) AS count


Or am I completely missing the point?


I've tried that.  It doesn't work for two reasons:

1) the ORDER BY subcat_count in the original query would cause an error in
the SQL
2) removing that ORDER BY clause to make valid SQL still results in a set of
rows - one for each of the original rows - rather than a single COUNT value
for the entire query.  The values for COUNT range from 1 to 4 depending on
how many subcategory ASSOC records (esa) are connected to a particular e.id.

Ah - that would be the group by doing that.

Removing those:

GROUP BY eta.id ORDER BY subcat_count DESC, eta.id

Does that get you what you want?

If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns.

--
Postgresql & php tutorials
http://www.designmagick.com/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux