Column is Adding rather than giving Average

jtrollinger

Why is AVG Punches Per House giving a total of Column and not Avg but PUNCH SCORE Column Total is giving AVG.

PUNCH SCORE Beast Mode

10 * (COUNT(DISTINCT SalesOrderNo) / COUNT(DISTINCT sonum_wtnumber))

AVG Punches Per House Beast Mode

(CASE WHEN HdrParentItemCode = 'ZPUNCH' AND JT158_WTParent = 'Y' THEN COUNT(DISTINCTsonum_wtnumber) END) / (CASE WHEN JT158_WTParent = 'Y' THEN (COUNT(DISTINCTSalesOrderNo)) END)

GrantSmith

Because your aggregation is within your case statement, it's getting treated differently.

Try this instead:

COUNT(DISTINCT CASE WHEN HdrParentItemCode = 'ZPUNCH' AND JT158_WTParent = 'Y' THEN sonum_wtnumber END) / COUNT(DISTINCT CASE WHEN JT158_WTParent = 'Y' THEN SalesOrderNo END)

GrantSmith

Because your aggregation is within your case statement, it's getting treated differently.

Try this instead:

COUNT(DISTINCT CASE WHEN HdrParentItemCode = 'ZPUNCH' AND JT158_WTParent = 'Y' THEN sonum_wtnumber END) / COUNT(DISTINCT CASE WHEN JT158_WTParent = 'Y' THEN SalesOrderNo END)

jtrollinger

Perfect! Thank you Grant!