Jump to content


Photo

want COUNT query for fairly elaborate query


  • Please log in to reply
3 replies to this topic

#1 sneakyimp

sneakyimp
  • New Members
  • Pip
  • Newbie
  • 1 posts

Posted 16 October 2006 - 12:43 AM

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.

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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 October 2006 - 02:23 AM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 16 October 2006 - 02:25 AM

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 ...

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)

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.

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 16 October 2006 - 02:39 AM

Leave it to shoz to fill in the blanks... ;-)
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users