SQL查询构建求助：Margin字段计算及除数非零处理问题

Hey there, the issue you’re facing is clear: when WERT equals 0, the division BRNTZ / WERT will throw a division-by-zero error (or return an unexpected NULL, depending on your SQL dialect). Let’s adjust the subquery to handle this edge case properly.

Here’s the revised query that avoids division by zero:

SELECT 
    ARTIKELNR, 
    ARTIKELTEXT, 
    B_DATA, 
    margin = (
        SELECT TOP 1 SUM(BRNTZ / NULLIF(WERT, 0) * 100) 
        FROM BEW 
        WHERE b.ARTIKELNR = a.ARTIKELNR AND USR_NR = $id
    ) 
FROM BEW b 
LEFT JOIN arb a ON b.ARTIKELNR = a.ARTIKELNR 
WHERE b.USR_NR = $id 
ORDER BY B_DATA DESC

What changed? #

I added NULLIF(WERT, 0) in the division. This function replaces any 0 value in WERT with NULL. When you divide by NULL, the result becomes NULL, which won’t trigger an error. If you’d prefer to return 0 instead of NULL when WERT is 0 (to align with your business logic), wrap the division in a COALESCE like this:

SUM(COALESCE(BRNTZ / NULLIF(WERT, 0), 0) * 100)

This way, if WERT is 0, the division becomes NULL, and COALESCE replaces it with 0 before multiplying by 100.

Quick side note: Make sure the TOP 1 in your subquery is intentional. If you meant to sum all matching rows instead of just the first one (which uses the database’s default unsorted order), you can remove the TOP 1 clause entirely.

内容的提问来源于stack exchange，提问作者Kafus