urgent: Trying to get COUNT for fairly elaborate query

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.06592416 764 -
z.long_radians)/2),2)))) < 50) GROUP BY eta.id ORDER BY subcat_count DESC,
eta.id




--
View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fair ly-elaborate-query-tf2449123.html#a6826536
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: 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: urgent: Trying to get COUNT for fairly elaborate query

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.


--
View this message in context: http://www.nabble.com/urgent%3A-Trying-to-get-COUNT-for-fair ly-elaborate-query-tf2449123.html#a6826826
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: 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: 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-fair ly-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: 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: 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-fair ly-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

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/

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: 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.06592416 764 -
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-fair ly-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