Page 1 of 2
[MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 1:35 pm
by nutkenz
Does anyone know what is wrong with this query?
SQL-query:
Code: Select all
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.
Re: [MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 1:38 pm
by s.dot
What's your mysql version? I believe subquery support isn't available until 4.1
Re: [MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 1:42 pm
by Christopher
The join should be something like:
keywords LEFT JOIN advertisers ON keywords.name=advertiser.name
Re: [MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 1:43 pm
by califdon
nutkenz wrote:Does anyone know what is wrong with this query?
SQL-query:
Code: Select all
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`).
Re: [MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 2:53 pm
by nutkenz
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
Re: [MySQL] Query syntax error?
Posted: Wed Feb 06, 2008 4:49 pm
by califdon
nutkenz wrote:scottayy wrote:
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
Oops, sorry! Give it an alias:
Code: Select all
SELECT COUNT(keyword) AS mycount FROM keywords LEFT JOIN advertisers ON advertiser_name = n
Re: [MySQL] Query syntax error?
Posted: Thu Feb 07, 2008 6:37 am
by nutkenz
Alias doesn't seem to help:
SQL-query:
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
Re: [MySQL] Query syntax error?
Posted: Thu Feb 07, 2008 10:45 am
by califdon
nutkenz wrote:Alias doesn't seem to help:
SQL-query:
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
Let's try this again:
Code: Select all
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
)
Re: [MySQL] Query syntax error?
Posted: Thu Feb 07, 2008 11:16 am
by nutkenz
I copied your code literally:
SQL-query:
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
Re: [MySQL] Query syntax error?
Posted: Fri Feb 08, 2008 12:37 am
by califdon
nutkenz wrote:I copied your code literally:
I really apologize. When I copied the code that time, I reverted back to the COUNT(*) syntax. Whoo! Let's see if I can get it right this time:
Code: Select all
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.
Re: [MySQL] Query syntax error?
Posted: Fri Feb 08, 2008 4:31 am
by nutkenz
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...
Re: [MySQL] Query syntax error?
Posted: Fri Feb 08, 2008 11:56 am
by califdon
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:
Code: Select all
$sql="SELECT * FROM table";
$results=mysql_query($sql);
$recordcount=mysql_num_records($result);
which you might want to do for comparison.
What exactly are you trying to get here? The number of records with a date named 'created' between two dates? Then what does the SUM() mean??
In any case, I don't see the need for the nested SELECTs. Why don't you try this:
Code: Select all
SELECT COUNT(keyword) FROM keywords
WHERE created BETWEEN '2007-01-07' AND '2007-02-06'
Re: [MySQL] Query syntax error?
Posted: Sat Feb 09, 2008 3:11 am
by nutkenz
It's the group by which is causing the difference:
$dbR = $db->get_results("SELECT * $from $where $group");
echo(count($dbR));
==> 2507
$dbR = $db->get_results("SELECT * $from $where");
echo(count($dbR));
==> 3525
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.
Re: [MySQL] Query syntax error?
Posted: Sat Feb 09, 2008 4:06 pm
by califdon
nutkenz wrote:It's the group by which is causing the difference:
$dbR = $db->get_results("SELECT * $from $where $group");
echo(count($dbR));
==> 2507
$dbR = $db->get_results("SELECT * $from $where");
echo(count($dbR));
==> 3525
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.
Re: [MySQL] Query syntax error?
Posted: Sun Feb 10, 2008 3:36 am
by Benjamin
The logic of your query is way off and it's missing aliases. Post your table structure and what your trying to accomplish.