SELECT SUM(
SELECT COUNT( * )
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword
)
MySQL returned:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM keywords LEFT JOIN advertisers ON advertiser_name = name WH' at line 1
When I only run the subquery by itself, then it does work... I've tried some variations but end up with the same error every time.
What's your mysql version? I believe subquery support isn't available until 4.1
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
SELECT SUM(
SELECT COUNT( * )
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword
)
MySQL returned:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(*) FROM keywords LEFT JOIN advertisers ON advertiser_name = name WH' at line 1
When I only run the subquery by itself, then it does work... I've tried some variations but end up with the same error every time.
I suspect the problem is that SUM() requires a specific field, whereas you're giving it a result set of the COUNT() of ALL fields. Try changing COUNT(*) to COUNT(`some specific field`).
scottayy wrote:What's your mysql version? I believe subquery support isn't available until 4.1
Version is 5.0.45
arborint wrote:The join should be something like:
keywords LEFT JOIN advertisers ON keywords.name=advertiser.name
Are you sure this matters because the query works fine on its own (without the SELECT SUM() wrapping it)
califdon wrote:
I suspect the problem is that SUM() requires a specific field, whereas you're giving it a result set of the COUNT() of ALL fields. Try changing COUNT(*) to COUNT(`some specific field`).
Unfortunately this results in the same error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(keyword) FROM keywords LEFT JOIN advertisers ON advertiser_name = n' at line 1
califdon wrote:
I suspect the problem is that SUM() requires a specific field, whereas you're giving it a result set of the COUNT() of ALL fields. Try changing COUNT(*) to COUNT(`some specific field`).
Unfortunately this results in the same error:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(keyword) FROM keywords LEFT JOIN advertisers ON advertiser_name = n' at line 1
SELECT SUM(
SELECT COUNT( keyword ) AS total
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword ) AS total
MySQL retourneerde:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(keyword) AS total FROM keywords LEFT JOIN advertisers ON advertiser' at line 1
SELECT SUM(
SELECT COUNT( keyword ) AS total
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword ) AS total
MySQL retourneerde:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT(keyword) AS total FROM keywords LEFT JOIN advertisers ON advertiser' at line 1
SELECT SUM(
SELECT COUNT( * ) AS total
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword
)
SELECT SUM(
SELECT COUNT( * ) AS total
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created
BETWEEN '2007-01-07'
AND '2007-02-06'
GROUP BY campaign_id, keyword )
MySQL retourneerde:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT COUNT( * ) AS total
FROM keywords
LEFT JOIN advertisers ON advert' at line 2
SELECT SUM(
SELECT COUNT( keyword ) AS total
FROM keywords
LEFT JOIN advertisers ON advertiser_name = name
WHERE created BETWEEN '2007-01-07' AND '2007-02-06'
GROUP BY campaign_id, keyword
)
Oh, wait a minute! The error message is telling us that what's wrong is the inner SELECT, used as the parameter of the SUM(). Ordinarily, a simple SUM() would be like SELECT SUM(fieldname) FROM tablename. Here, I think you want to return the sum of the count of keywords from multiple campaigns, right? It looks to me like you don't want any GROUP BY in there, just the JOIN and the WHERE clause. But if that's true, then you don't need the second SELECT at all. Sorry, it has been a long day and I'm confused.
nutkenz wrote:You're right... I am confused because I'm doing two queries like this:
SELECT COUNT(*) AS total $from $where
This returns 4018, though when I try this query:
SELECT * $from $where $group $order LIMIT 4000,50
Then I get an empty set of results... The actual number of results seems to be 2936 because that's the last page I can results of. Very strange...
I'd have to check the SQL manual, but possibly by using COUNT(*) it may return a count of EACH field (which doesn't make much sense, I'm just rambling here). You might try doing a COUNT() of each field, separately. You can also get a straightforward count of records in a table like this:
So I still need to find a way to include it in the query and get the sum of the results because getting them all and then counting them in PHP seems very inneficient.
So I still need to find a way to include it in the query and get the sum of the results because getting them all and then counting them in PHP seems very inneficient.
Yes, of course the grouping will give different totals, but that's not what caused an error to be reported, that was an error in your SQL syntax. Do you mind telling us exactly what number you're trying to arrive at? Is it the number of keyword records created during a certain time period with matching advertisers' names? That's what it looks to me as if you're trying to get, and if it is, you don't need hardly any of that syntax--no GROUP BYs and probably not even a JOIN. Maybe you're looking for something else, but I haven't seen what that might be.