Re: urgent: Trying to get COUNT for fairly elaborate query
| From: | sneakyimp | Date: | Mon, 16 Oct 2006 22:06:18 +0000 |
| Subject: | Re: urgent: Trying to get COUNT for fairly elaborate query | ||
| References: | 1 2 3 4 5 6 7 8 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-42568@lists.php.net to get a copy of this message | ||
chris smith-9 wrote:
>
>
> 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;
>
I'm using 4.0.0. I found a solution that worked:
SELECT COUNT(DISTINCT eta.id) 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))
Also, there is a feature in MySQL 4.0.0 and higher where you can add
'SQL_CALC_FOUND_ROWS' to your query and run it *with the LIMIT clause* then
you simply run another query for FOUND_ROWS() immediately after and MySQL
will return the total number of rows that *would be returned without the
limit clause*.
like this:
SELECT SQL_CALC_FOUND_ROWS 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
LIMIT 0, 50
then you run this query:
SELECT FOUND_ROWS() AS total_records
THANKS for your effort chris.
--
View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6844499
Sent from the Php - Database mailing list archive at Nabble.com.