Question

Why aggregation functions does not work?

  • 23 August 2021
  • 1 reply
  • 44 views

My aggregation functions are not working!

I am expecting calculation results AVG, SUM, MAX in measures, but getting only field value

Can anyone identify what I'm doing wrong?

explore: test2 {
symmetric_aggregates: yes
}

view: test2 {
  derived_table: {
    sql:
    WITH EXAMPLE_TABLE AS (
      SELECT 1 as ID, 1 as AGE, "Peter" as name, "M" as gender
      UNION ALL
      SELECT 2 as ID, 2 as AGE, "Oleg" as name, "M" as gender
      UNION ALL
      SELECT 3 as ID, 4 as AGE, "Ian" as name, "M" as gender
      UNION ALL
      SELECT 4 as ID, 15 as AGE, "Olga" as name, "F" as gender
      UNION ALL
      SELECT 5 as ID, 16 as AGE, "Diana" as name, "F" as gender
      UNION ALL
      SELECT  6 as ID, 50 as AGE, "Janet" as name, "F" as gender
    )
    SELECT  *    FROM EXAMPLE_TABLE    ;;
  }



  dimension: name {
    description: "Name"
    sql: ${TABLE}.name ;;

  }
  dimension: AGE {
    description: "AGE"
    type: number
    sql: ${TABLE}.age ;;
    html: <b>{{ value }}</b> ;;
  }

  dimension: gender {
    description: "Gender"
    type: number
    sql: ${TABLE}.gender ;;
    html: <b>{{ value }}</b> ;;
  }

  measure: avg_age1 {
    description: "AVG_AGE1"
    # type: sum
    type: average
    hidden:  no
    sql: age ;;
  }
  
  measure: AVG_AGE2 {
    description: "AVG_AGE2"
    sql: AVG(${AGE}) ;;
    # value_format_name: usd
  }
  
  measure: MAX_AGE {
    description: "MAX_AGE"
    type:sum

    sql: ${TABLE}.age ;;
  } 
}

The result is just copy of value from field

But I expect 50 in all rows in column MAX_AGE   

For example 

  measure: MAX_AGE {
description: "MAX_AGE"
type:sum
sql: (select max(age) from ${TABLE}) ;;
}

  Does the work, but problematic for  filtering


This topic has been closed for comments

1 reply

The question is closed. 
I solved my problem