Multi tool use

SQL: SUM in the middle of a SELECT query fails

I have the following tables:

Invoice

InvoiceLine

I want to have the following: The company name and total amount of invoice with ID 1760.

I use the following query:

SELECT i.*, sum(il.amount) as 'Total'
FROM invoice i JOIN invoiceLine il on i.invoiceID = il.InvoiceID
WHERE i.InvoiceID = 1760


I get this as error:

Column 'invoice.invoiceID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Why is the error returned and why does it want me to use a GROUP BY clause while i'm only expecting one row to be returned? Would I be obligated to do this in 2 queries?

i.*, SUM(il.amount)

3 Answers
3

You will need a GROUP BY for each column that you are not aggregating

Try this:

SELECT i.invoiceID, i.companyName, sum(il.amount) as 'Total'
FROM invoice i JOIN invoiceLine il on i.invoiceID = il.InvoiceID
WHERE i.InvoiceID = 1760
GROUP BY i.invoiceID, i.companyName


You need to specify columns to group by when performing aggregate functions.

SELECT i.InvoiceID,i.CompanyName, sum(il.amount) as 'Total'
FROM invoice i JOIN invoiceLine il on i.invoiceID = il.InvoiceID
WHERE i.InvoiceID = 1760
group by i.InvoiceID,i.CompanyName


You can use a correlated subquery:

SELECT i.*,
(SELCT sum(il.amount)
FROM invoiceLine il
WHERE i.invoiceID = il.InvoiceID
) as Total
FROM invoice i
WHERE i.InvoiceID = 1760;


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.