SQL select Sum of joined table

May 24, 2024

I have two tables. Jobs and Parts. One Job can have many parts.

I want to list all the jobs, and then a column showing the sum of parts for that job.

My code (in Firebird) is below. I just get error – Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause).

Thank you.

SELECT J.ID, J.REF, J.CUST, J.DATEADDED, Sum(P.QTYREQ)
FROM JOBS J
Join PARTS P 
    on J.ID = P.JOBID 
Group by J.ID

>Solution :

When you put a SUM() in the Select section with other elements, you need to specify how all the non-aggregated elements group by. So:

SELECT J.ID, J.REF, J.CUST, J.DATEADDED, SUM(P.QTYREQ) AS TotalParts 
FROM JOBS J 
JOIN PARTS P ON J.ID = P.JOBID 
GROUP BY J.ID, J.REF, J.CUST, J.DATEADDED;