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

sneakyimp wrote:

The original query results (minus most of the fields but including the COUNT(esa.id) part) would look something like this: id title subcat_count 60 Another Halloween Party 4 50 Satan's Midnight October Bash 1 61 Halloween IPN Testing party 1 19 test 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 64 I happen more than once today 1 If I adjust the query, using only a COUNT(*) in the select part and leaving the GROUP BY stuff (which is necessary to avoid counting each ESA table association more than once) then I get a query like this: === SELECT COUNT(*) AS 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))

GROUP BY eta.id === which gives me this as a result: count 1 4 <-- for this particular e.id, there are multiple entries in the ESA table 1 1 1 1 1 1 1 1 1 NOTE: there is one row in this result for each of the original rows. so the ROWCOUNT is the same and still correct at 11 rows. Ultimately, what I want is a query that returns ONLY THE ROW COUNT OF THE ORIGINAL QUERY. In this case, 11.

What mysql version are you using? I wonder if you can use this part as a subquery and then: select sum(foo) as "total" from (SELECT COUNT(*) AS 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))

GROUP BY eta.id) AS foo; -- Postgresql & php tutorials http://www.designmagick.com/