[MySQL] Query syntax error?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

[MySQL] Query syntax error?

Post 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.
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Re: [MySQL] Query syntax error?

Post by s.dot »

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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: [MySQL] Query syntax error?

Post by Christopher »

The join should be something like:

keywords LEFT JOIN advertisers ON keywords.name=advertiser.name
(#10850)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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`).
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Re: [MySQL] Query syntax error?

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Re: [MySQL] Query syntax error?

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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
    )
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Re: [MySQL] Query syntax error?

Post 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
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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.
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Re: [MySQL] Query syntax error?

Post 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...
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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'
nutkenz
Forum Contributor
Posts: 155
Joined: Tue Jul 19, 2005 12:25 pm

Re: [MySQL] Query syntax error?

Post 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.
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: [MySQL] Query syntax error?

Post 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.
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Re: [MySQL] Query syntax error?

Post 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.
Post Reply