Combine select and update statement in query

Question

I would like to set all blank columns of my query to "0", so that I can use it for further calculations.

My query looks as follows:

SELECT 
    SUM(Erstattungen.Betrag) AS Gesamtbetrag_Erstattungen,
    Mitarbeiter.Referent_Name
FROM 
    Mitarbeiter 
LEFT JOIN 
    Erstattungen ON Mitarbeiter.Mitarbeiter_ID = Erstattungen.Mitarbeiter_ID
GROUP BY 
    Mitarbeiter.Referent_Name, Erstattungen.Mitarbeiter_ID;

and it returns empty columns for Gesamtbetrag_Erstattungen as a result of the left join.

I would like to fill these empty columns with "0" instead of having a blank space there.

What would be a good way to do that? Maybe use an additional update function? How could I do that?

Thanks in advance.

Answer 1

The "empty" fields are NULLs caused by the LEFT JOIN. Because of the LEFT JOIN, all aggregation columns should come from the first table.

You can then use NZ() to replace the values:

SELECT NZ(Sum(e.Betrag), 0) AS Gesamtbetrag_Erstattungen, m.Referent_Name
FROM Mitarbeiter as m LEFT JOIN
     Erstattungen as e
     ON m.Mitarbeiter_ID = e.Mitarbeiter_ID
GROUP BY m.Referent_Name, m.Mitarbeiter_ID;

Table aliases make the query easier to write and to read. Note the change to using m.Mitarbeiter_ID instead of e.Mitarbeiter_ID and NZ().