Why am i getting a Syntax error using the following code

Question

I am using the following statement to develop a simple query. Unfortunately I continually get a syntax error when I run the page. If I take out either of the RaisedInvoice Select or the Transaction Sum elements it works but fails when both are in. It must be something stupid so, can I please apologise in advance for my silly mistake.

SELECT 
RaisedInvoices.[Invoice Number], SUM(Transactions.sellgross) AS SumOfsellgross 
FROM (RaisedInvoices LEFT JOIN Customers ON RaisedInvoices.[Customer WORef] = Customers.WORef) 
LEFT JOIN Transactions ON RaisedInvoices.[Invoice Number] = Transactions.invnumber

PDO doesn't execute your queries. You are getting a Syntax error from your database server. — Your Common Sense
– Your Common Sense, Commented Apr 8, 2021 at 13:03

The Impaler · Accepted Answer · 2021-04-08 13:21:19Z

2

You are aggregating one column but not the other one. In this case, you need to include a GROUP BY clause with the non-aggregated column(s).

For example:

SELECT 
  RaisedInvoices.[Invoice Number], -- not aggegated
  SUM(Transactions.sellgross) AS SumOfsellgross -- aggregated
FROM RaisedInvoices 
LEFT JOIN Customers 
      ON RaisedInvoices.[Customer WORef] = Customers.WORef
LEFT JOIN Transactions 
      ON RaisedInvoices.[Invoice Number] = Transactions.invnumber
GROUP BY RaisedInvoices.[Invoice Number] -- added GROUP BY here

answered Apr 8, 2021 at 13:21

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Romain biard · Accepted Answer · 2021-04-08 13:05:07Z

0

Based on your query, I would say that you don't need the join with the Customers table.

Here is how I would write it:

$query = "SELECT RaisedInvoices.[Invoice Number], SUM(Transactions.sellgross) AS SumOfsellgross FROM RaisedInvoices LEFT JOIN Transactions ON RaisedInvoices.[Invoice Number] = Transactions.invnumber";

However, I'm not sure that the brackets syntax is something that the PDO will accept.

answered Apr 8, 2021 at 13:05

Romain biard

1869 bronze badges

Collectives™ on Stack Overflow

Why am i getting a Syntax error using the following code

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related