Jump to content

Archived

This topic is now archived and is closed to further replies.

sneakyimp

want COUNT query for fairly elaborate query

Recommended Posts

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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Leave it to shoz to fill in the blanks... ;-)

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.