Re: [PHP-DB] 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/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
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. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: 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. Doing this is actually rather easy. Replace this: 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 With: SELECT COUNT(e.id) AS count Or am I completely missing the point? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: chris smith-9 wrote: Doing this is actually rather easy. Replace this: 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 With: SELECT COUNT(e.id) AS count Or am I completely missing the point? I've tried that. It doesn't work for two reasons: 1) the ORDER BY subcat_count in the original query would cause an error in the SQL 2) removing that ORDER BY clause to make valid SQL still results in a set of rows - one for each of the original rows - rather than a single COUNT value for the entire query. The values for COUNT range from 1 to 4 depending on how many subcategory ASSOC records (esa) are connected to a particular e.id. Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
chris smith-9 wrote: Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. I really appreciate your help by the way. I've also tried that. I do get a single value when I do that but the count is too high! Each of the esa associations gets counted separately rather than just once. The original COUNT and GROUP BY parts limit the number of records by aggregating all the esa records per event into a single count - which is what i want. -- View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6827251 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
sneakyimp wrote: chris smith-9 wrote: Ah - that would be the group by doing that. Removing those: GROUP BY eta.id ORDER BY subcat_count DESC, eta.id Does that get you what you want? If it gives you one result - make sure it's right. Change a few id's, make sure they match up to what your other query returns. I really appreciate your help by the way. I've also tried that. I do get a single value when I do that but the count is too high! Each of the esa associations gets counted separately rather than just once. The original COUNT and GROUP BY parts limit the number of records by aggregating all the esa records per event into a single count - which is what i want. I don't understand. What results do you get from the original query (just the 2-3 columns we need please) ? What does the new query (that doesn't work) give you? What do you want to get from the count query? -- Postgresql php tutorials http://www.designmagick.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query
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. If I remove the GROUP BY part of the query then i can get a single result...this is what i want, HOWEVER, the count doesn't match the number of rows in the original query. that one event that has 4 subcategories associated with is counted once for each subcategory association which means my count is too high be the extra 3 records. the 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 ) ) === returns this: count 14 -- View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fairly-elaborate-query-tf2449123.html#a6827498 Sent from the Php - Database mailing list archive at Nabble.com. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php