COUNT field incorrect or syntax error

Question

What would be the error when I get following error message

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error'...

This is the query I'm using

$sql = $pdo->prepare("SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
    JOIN stock ON stockbalance.stockid = stock.stockid
    LEFT JOIN reservationtransaction ON reservationtransaction.articleid = :artid
    WHERE stockbalance.articleid = :artid AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
    GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->execute();

I have searched questions in SO, but no one was similar or helpful.
Thanks in advance.

Edit: This query is working fine when executing it with Microsoft SQL Server Management Studio, but when using PDO, I'm getting the error.

Your `stockamount` and `stockname` are not part of the `GROUP BY` so you cannot select them without an aggregate method like `MIN`. — Tim Schmelter, Dec 04 '15 at 13:39
I guess you have MySQL background :http://stackoverflow.com/questions/33629168/group-by-clause-in-mysql-and-postgresql-why-the-error-in-postgresql/33629201#33629201. The point is MySQL aggregation behaviour isn't ANSI complaint. — Lukasz Szozda, Dec 04 '15 at 13:41
Per the error message, this is a PDOException, not a SQL Exception. Could it be a mismatch in the result set, and the object you're trying to bind it to? — Tab Alleman, Dec 04 '15 at 14:49
@TabAlleman, everything works ok when I'm executing this with Microsoft SQL Server Management Studio, but when using PDO, I'm getting this error. I also tried without `bindValue()` and writing values to the query - same error. — lingo, Dec 06 '15 at 20:26

score 51 · Accepted Answer · answered Dec 08 '15 at 14:42

The number of parameters specified in SQLBindParameter was less than the number of parameters in the SQL statement contained in *StatementText. SQLBindParameter was called with ParameterValuePtr set to a null pointer, StrLen_or_IndPtr not set to SQL_NULL_DATA or SQL_DATA_AT_EXEC, and InputOutputType not set to SQL_PARAM_OUTPUT, so that the number of parameters specified in SQLBindParameter was greater than the number of parameters in the SQL statement contained in *StatementText. SQLExecute Function

placeholders must have unique names even if they have the same value

$sql = $pdo->prepare("SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
JOIN stock ON stockbalance.stockid = stock.stockid
LEFT JOIN reservationtransaction ON reservationtransaction.articleid = :artid
WHERE stockbalance.articleid = :artid2 AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->bindValue(':artid2', $productId);
$sql->execute();

You have no idea how much time this answer has saved me! Thank's a lot!!! — dVyper, Mar 05 '21 at 16:16
Why Microsoft would make the developer's life *easier*, let them burn in *bind parameters** hell ?! — Bud Damyanov, Mar 06 '21 at 10:26

score 5 · Answer 2 · answered Mar 11 '18 at 22:51

Another possibility, if you want to avoid supplying data multiple times (replace the datatype of @artid with the correct data type):

$sql = $pdo->prepare("DECLARE @artid int = :artid
    SELECT stockamount, stockname, stockbalance.stockid, SUM(ABS(reservationtransaction.stockquantity)) AS reservedamount FROM stockbalance
    JOIN stock ON stockbalance.stockid = stock.stockid
    LEFT JOIN reservationtransaction ON reservationtransaction.articleid = @artid
    WHERE stockbalance.articleid = @artid AND ((changeddate > DATEADD(yy,-1,GETDATE()) AND inventorydate > DATEADD(yy,-1,GETDATE())) OR stockbalance.stockamount <> 0)
    GROUP BY stockbalance.stockid");
$sql->bindValue(':artid', $productId);
$sql->execute();

This will only work in an RDBMS that supports DECLARE statements.

score 1 · Answer 3 · edited Dec 04 '15 at 13:42

All the columns that are not in any arithmetic function must go in the GROUP BY clause. see below:

SELECT stockamount, 
       stockname, 
       stockbalance.stockid, 
       Sum(Abs(reservationtransaction.stockquantity)) AS reservedamount 
FROM   stockbalance 
       INNER JOIN stock 
               ON stockbalance.stockid = stock.stockid 
       LEFT JOIN reservationtransaction 
              ON reservationtransaction.articleid = :artid 
WHERE  stockbalance.articleid = :artid 
       AND ( ( changeddate > Dateadd(yy, -1, Getdate()) 
               AND inventorydate > Dateadd(yy, -1, Getdate()) ) 
              OR stockbalance.stockamount <> 0 ) 
GROUP  BY stockamount, 
          stockname, 
          stockbalance.stockid

Ok, I tried that, but it doesn't help. I'm still getting error `SQLSTATE[07002]: [Microsoft][ODBC Driver 11 for SQL Server]COUNT field incorrect or syntax error`. — lingo, Dec 04 '15 at 13:44

score 1 · Answer 4 · answered May 01 '19 at 16:22

While GROUP BY is often the culprit for a COUNT issue, I ran into the subject error in SSRS and it was due to a mismatch between the SSRS dataset query and the parameters. The resolution was to ensure each WHERE in the query had a corresponding parameter for the dataset. (I had missed one.)

score 0 · Answer 5 · answered Mar 18 '22 at 09:09

We had this error when a Django filter statement was trying to substitute more parameters in a query than could be handled by the DB binary. Our query looked roughly like this:

select name, age from Employee where emp_id in (%s)

from a Django statement

Employee.objects.filter(emp_id__in=employee_ids)

The list of emp_id was about 2,100 elements. (The character length of the params substituted was about 10,500).

The hack was to filter in parts and recombine the subsets.

COUNT field incorrect or syntax error

5 Answers5