how to implement this query

[lhu]

The table t has 3 columns and the value of each col is 0 or 1,see the below:

a b c

1 0 1

0 1 1

1 1 0

...

I want to get the 3 results like:

select count(*) as cnt_a from t where a=1;

select count(*) as cnt_b from t where b=1;

select count(*) as cnt_c from t where c=1;

if i do it using the 3 queries above,the supersonic will iterates the whole table 3 times.

how to do it in one query using supersonic?

thx~

[ptab]

If you implement this as 3 queries, supersonic will do 3 passes. 

But I think that you really want to execute:

SELECT SUM(a), SUM(b), SUM(c) FROM t; 

And its just GroupAndAggregate from Supersonic.

[lhu]

Thanks for quick reply!

In fact,the column type is BOOL,SUM can still solve it?

If not,is there any other way?

在 2014年1月10日星期五UTC+8下午7时41分20秒，ptab写道：

[Milosz Tanski]

You can use a Cast expression in Your query.

This email was sent from a tiny keyboard.

--
You received this message because you are subscribed to the Google Groups "Supersonic Query Engine Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to supersonic-query-...@googlegroups.com.
To post to this group, send an email to supersonic-...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

[Piotr Tabor]

So in Supersonic you can have 2 steps: 

 1 compute: with three expressions:    IF(A, 1, 0), IF(B, 1, 0), IF(C, 1, 0)

 And than GroupAndAggregate with sums. 

I'm not sure if we support in Supersonic direct Cast (also an expression that needs to be used in Compute) from BOOL to INT. I would guess so, but no promise.   

[lhu]

Thanks for your help!

I tried Cast,but get an error:Cannot cast BOOL to Int32.

Then ,i tried using compute,but unfortunately still get an error:Incorrect aggregation specification.Aggregation input column does not exist:a

The code like: 

     spec->AddAggregation(SUM,"a","cnt_a“);

     ScalarAggregate(spec.release(),Compute(If(Equal(ConstBool(true),NamedAttribute("a")),ConstInt32(1),ConstInt32(0)),ScanView(table.view())));

I want to known where can i set the output name ? And how add three If to Compute,nested compute?

Best regards!

在 2014年1月12日星期日UTC+8上午12时24分36秒，ptab写道：

To unsubscribe from this group and stop receiving emails from it, send an email to supersonic-query-engine+unsub...@googlegroups.com.

To post to this group, send an email to supersonic-...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

--
You received this message because you are subscribed to the Google Groups "Supersonic Query Engine Group" group.

To unsubscribe from this group and stop receiving emails from it, send an email to supersonic-query-engine+unsub...@googlegroups.com.

[ptab]

When you wrap expression it looses its name:

Try: using Alias operator in the Expression:  https://code.google.com/p/supersonic/source/browse/supersonic/expression/core/projecting_expressions.h#68  

spec->AddAggregation(SUM,"a_tmp","cnt_a“);

ScalarAggregate(spec.release(),Compute(Alias("a_tmp",If(Equal(ConstBool(true),NamedAttribute("a")),ConstInt32(1),ConstInt32(0))),ScanView(table.view())));