get sum of all rows with id from main query

I need the sum of all positions from one year for every customer. In the subquery i’ve hardcoded the customernumber (mark 1) but it needs to be another customernumber for every row. The values in mark 2 are wrong.

SELECT CustomerNumber AS Kundennummer,
    cont.CompanyName AS Firmenname,
    (SELECT SUM(SumPositions) FROM OP_Invoices JOIN BAS_Customers ON OP_Invoices.Contact = BAS_Customers.Contact WHERE YEAR(DocumentDate) = 2018 AND BAS_Customers.CustomerNumber = '28673') AS '2018'
    FROM BAS_Customers AS cust
    JOIN BAS_Contacts AS cont
        ON cust.Contact = cont.GUID

Thanks for your help!

Answer

If BAS_Customers and BAS_Contacts have a one-to-one relationship, then you should be able to use something like this:

SELECT      BAS_Customers.CustomerNumber AS Kundennummer,
            cont.CompanyName AS Firmenname,
            SUM(SumPositions)
FROM        OP_Invoices 
JOIN        BAS_Customers 
            ON OP_Invoices.Contact = BAS_Customers.Contact 
JOIN        BAS_Contacts AS cont
            ON cust.Contact = cont.GUID
WHERE       YEAR(DocumentDate) = 2018 
GROUP BY    BAS_Customers.CustomerNumber,
            cont.CompanyName

Answering your follow up question in the comments:

SELECT      BAS_Customers.CustomerNumber AS Kundennummer,
            cont.CompanyName AS Firmenname,
            SUM(case when YEAR(DocumentDate) = 2018 then SumPositions else 0 end) AS sum_2018,
            SUM(case when YEAR(DocumentDate) = 2019 then SumPositions else 0 end) AS sum_2019
FROM        OP_Invoices 
JOIN        BAS_Customers 
            ON OP_Invoices.Contact = BAS_Customers.Contact 
JOIN        BAS_Contacts AS cont
            ON cust.Contact = cont.GUID
GROUP BY    BAS_Customers.CustomerNumber,
            cont.CompanyName