Why can't I use a measure outside an aggregate context?

[jwhitaker-gridcog]

Let's say I have a table of sales:

item	total
sku1	4
sku2	8
sku1	4

I can define a malloy source for this:

source_raw: sales is duckdb.sql("""
	select * from ( values ('sku1', 4), ('sku2', 8), ('sku1', 4)) t(item, total)
""")

And I can define a measure for total:

source: sales_model is sales extend {
	rename:
		_total is total
	measure:
		total is sum(_total)
}

However, I want to be able to use the measure total even if I'm not in a group by context. Intuitively this should be fine, right? (if we forget the chains of SQL for a second..) - the sum() of a single number is perfectly reasonable to take.

However, if I do

run: sales_model -> {
    select: item, total
}

i of course get

Cannot use an aggregate field in a select operation, did you mean to use an aggregate operation instead?

so now I need to be careful to remember to use _total or total depending on what I'm doing (=> just like in sql).

What I really want to be able to do is to define something like

source: sales_model is sales extend {
    measure: total is sum(total)
}

And then be allowed to query total whether I am grouping or not. I guess this could be interpreted in a few different ways

• either as if there some hidden scoping system that checks measures before dimensions
• or as if I can attach some metadata to a dimension that either lets me use it as a measure, or forces me to use it as a measure (but still lets me select it);
• or as if all/some measure functions have a well-defined "single row" value and as such are permitted to appear in select. This last one is interesting, it might be (theoretically) true? count => 1, sum(x) => x, avg(x) => x, etc..?

I haven't used Looker but I'm pretty sure in PowerBI and Tableau this sort of thing is straightforward - in PowerBI it would look like setting a "Default Aggregation" for a field.

[lloydtabb]

Really interesting suggestion. Thanks @mtoy-googly-moogly @carlineng @christopherswenson (for context).

We have thought about this and currently we allow dimensions to be summed without renaming them.

  run: sales_model -> {
    aggregate: 
       total.sum()
       average_total  is total.avg() 
  }

I think it gives you much of what you want, though your suggestion pushes the calculation into the model. Sum is the only combinator that is allowed to use this format. Combinators .avg(), min() and max() would all need to have different names than the base dimension.