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

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/