Column is Adding rather than giving Average

jtrollinger
jtrollinger Member
in Beast Mode

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)

image.png

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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)

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jtrollinger
    jtrollinger Member

    Perfect! Thank you Grant!

Categories