SQL left join from a mix of tables and columns throwing a "SQLSTATE[42000]: Syntax error or access violation 8120" error

Question

this is my query:

$sql = "SELECT sum(CommitmentItemInvoices.Amount) AS PendTotal, CommitmentItemInvoices.Amount AS Amount_Pending , CommitmentItems.*, CommitmentItemInvoices.*
                    FROM CommitmentItemInvoices 
                    LEFT JOIN CommitmentItems on CommitmentItems.commitmentItemId = CommitmentItemInvoices.commitmentItemId
                    WHERE (CommitmentItemInvoices.Status='new' AND CommitmentItemInvoices.commitmentItemId = :commitId)";
                try{
                    //prepare statement
                    $stmt = $con->prepare ($sql);

                    //bind values to :username
                    $stmt->bindValue("commitId", $commitId, PDO::PARAM_STR);


                    $stmt->execute();
                }catch(PDOException $e){
                    echo "Error: ".$e->getMessage();
                }

I am getting the following error:

Error: SQLSTATE[42000]: Syntax error or access violation: 8120 [Microsoft][ODBC SQL Server Driver][SQL Server]Column 'CommitmentItemInvoices.Amount' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. (SQLExecute[8120] at ext\pdo_odbc\odbc_stmt.c:254)

Any idea as to what I am doing wrong in the SQL query?

You've tagged mysql as your dbms, but the error is clearly from SQL Server. Which is it? — crthompson
First, looks like you're using SQL Server? Your issue is that you have left out the group by clause. You need to include those columns not included in an aggregate function in the group by clause. — sgeddes
Eliminate these columns from the select and re-run. it works right? CommitmentItemInvoices.Amount AS Amount_Pending , CommitmentItems.*, CommitmentItemInvoices.* Now just add back in the amount one.. does it work? No? Look at how aggregates work and the fact they need group by. mySQL extends the group by so that it's not always needed, but SQL Server doesn't. — xQbert
@xQbert excellent suggestion, I've noted it in my answer. xGrave01 Take it step by step. Do you know how you want to aggregate? — crthompson

crthompson crthompson · Accepted Answer · 2014-12-16T16:00:38

You need a GROUP BY statement to allow the aggregate to work:

SELECT sum(cii.Amount) AS PendTotal
FROM CommitmentItemInvoices cii
LEFT JOIN CommitmentItems ci on 
         ci.commitmentItemId = cii.commitmentItemId
WHERE (cii.Status='new' 
  AND cii.commitmentItemId = :commitId)

@xQbert makes an excellent suggestion. Pull out all the fields and you'll see that the query works. Then add them one by one into the select and group by portion of your query. You'll see how the aggregation works.

The * in your query doesnt work because the sql engine needs to know how to aggregate your sum. You can aggregate by many fields, but they each need to be specified in the select AND in the group by.

You will likely get different results as you add more fields to group by.

Read up on GROUP BY to determine which fields you want to group to get the correct aggregation.

SQL left join from a mix of tables and columns throwing a "SQLSTATE[42000]: Syntax error or access violation 8120" error

1 Answers