want COUNT query for fairly elaborate query

[sneakyimp]

See this query?  I need a separate query that will return ONLY the total record count that it would come up with.  I've tried replacing the select part with COUNT() but I still get a series of records in my return result.  I just need ONE return value -- the total COUNT of rows returned by this query originally.

[code]
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
FROM demo_event_time_assoc eta,
        demo_events e,
        demo_event_subcategory_assoc esa,
        demo_zip_codes z
WHERE eta.event_id=e.id
        AND esa.event_id=e.id
        AND z.zip=e.zip
        AND e.active=1
        AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
        AND (
        (eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
        OR
        (eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
        AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.59533458956 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50) GROUP BY eta.id ORDER BY subcat_count DESC, eta.id
[/code]

[fenway]

Well, you have a GROUP BY clause, so if you just put a COUNT(*), you're still going to get a count for each group.  If you don't care about this anymore, you'll need different query logic.

[shoz]

If you're looking for the number of rows that the query would return you can use the following. Note the removal of the GROUP BY ...

[code]
SELECT COUNT(DISTINCT eta.id) AS total
FROM demo_event_time_assoc eta,
        demo_events e,
        demo_event_subcategory_assoc esa,
        demo_zip_codes z
WHERE eta.event_id=e.id
        AND esa.event_id=e.id
        AND z.zip=e.zip
        AND e.active=1
        AND esa.subcategory_id IN (1,2,3,4,5,6,7,8,9,10)
        AND (
        (eta.start_timestamp >= 1162281600 AND eta.start_timestamp <= 1162368000)
        OR
        (eta.end_timestamp < 1162281600 AND eta.end_timestamp > 1162285200))
        AND (3963.20477315*(2*asin(sqrt(POWER(sin((0.59533458956 -z.lat_radians)/2),2) + cos(0.59533458956)*cos(z.lat_radians)*POWER(sin((-2.06592416764 - z.long_radians)/2),2)))) < 50)
[/code]

If you're looking for the number of rows that match the WHERE clause and consequently affects the end result of the query, change "COUNT(DISTINCT...)" to "COUNT(*)" as suggested.

[fenway]

Leave it to shoz to fill in the blanks... ;-)