COUNT field incorrect or syntax error
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.
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();
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