Re: [PHP-DB] urgent: Trying to get COUNT for fairly elaborate query

2006-10-16 Thread Chris

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

2006-10-16 Thread sneakyimp



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

2006-10-15 Thread Chris

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

2006-10-15 Thread Chris

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

2006-10-15 Thread sneakyimp



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

2006-10-15 Thread Chris

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

2006-10-15 Thread sneakyimp

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