How to ignore group by

[djouallah]

in this example

source: names_table is table('duckdb:lineitem.parquet') {
  measure : Quantity is L_QUANTITY.sum()
  measure: Quantity_RETURNFLAG_A is Quantity { where: L_RETURNFLAG = 'N' }

    Query: by_returnflag is {
    group_by : L_RETURNFLAG
    aggregate : Quantity
    aggregate : Quantity_RETURNFLAG_A
 }

it return this results

L_​RETURNFLAG	Quantity	            Quantity_​RETURNFLAG_​A	
N	                          774,486,807	    774,486,807	
R	                          377,732,830	       ∅	
A	                          377,518,399          ∅	

How to get Quantity_RETURNFLAG_A to give the same number regardless of the grouping ?

[lloydtabb]

I'm not sure I understand the question but is this what you're asking about? https://looker-open-source.github.io/malloy/documentation/patterns/percent_of_total.html

…

Message ID: ***@***.***>

[djouallah]

Thanks, but it is too complex, ideally something like sum All Quantity at the measure level will be really useful.

[lloydtabb]

Soon. #618

…

On Wed, Jul 20, 2022, 8:48 PM Mimoune ***@***.***> wrote: Thanks, but it is too complex, ideally something like sum All Quantity at the measure level will be really useful. — Reply to this email directly, view it on GitHub <#622 (reply in thread)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AAIK6UTU7E2CFM3YFTBHHITVVDCBVANCNFSM54F7XL2A> . You are receiving this because you commented.Message ID: ***@***.*** com>

[djouallah]

Thanks, the measure Quantity_​RETURNFLAG_​A seems to return wrong results when using nested Query ?

[lloydtabb]

Thanks for the report.. I'm not able to reproduce your issue exactly, but I've found two bugs related (our duckdb implementation is early, this works on BigQuery).

sum() should never return NULL, always 0, much like count()

Our duckdb node driver isn't handing nulls in numbers correctly, it seems and crashing form (and maybe giving incorrect results for you).

try

  measure: Quantity is IFNULL(L_QUANTITY.sum(), 0)

[lloydtabb]

#623

#624

[djouallah]

Thanks
measure: Quantity is IFNULL(L_QUANTITY.sum(), 0)
gave me the correct results