Re: Group By and Sum

[Miguel]

Hi Adam

    Have you seen wiki about field collapsing?
http://wiki.apache.org/solr/FieldCollapsing

I think that this page help you to emule group by.



El 18/03/2013 17:48, Adam Harris escribió:

Hello All,

Pretty stuck here and I am hoping you might be the person to help me out. I am 
working with SOLR and JSONiq which are totally new to me and doing even the 
simplest of things is just escaping me. I know SQL pretty well however this 
simple requirement seems escape me. I'll jump right into it.

Here is the schema of my Core:

<fields>



    <field name="BuID" type="int" indexed="true" stored="true" required="true"/>

    <field name="BusinessDate" type="text_general" indexed="true" stored="true" 
required="true"/>

    <field name="BusinessDateTime" type="date" indexed="true" stored="true" />

    <field name="Name" type="text_general" indexed="true" stored="true" 
required="true"/>

    <field name="BeginTime" type="text_general" indexed="true" stored="true" 
required="true"/>

    <field name="BeginDateTime" type="date" indexed="true" stored="true" />

    <field name="TransCount" type="int" indexed="true" stored="true" 
required="true"/>

    <field name="NetSales" type="float" indexed="true" stored="true" 
required="true"/>



</fields>

I need to group by the month of BusinessDateTime and sum up NetSales and 
TransCount for a given date range. Now if this were SQL i would just right


SELECT sum(TransCount), sum(NetSales)

FROM Core

WHERE BusinessDateTime BETWEEN '2012/04/01' AND '2013/04/01'

GROUP BY MONTH(BusinessDateTime)

But ofcourse nothing is this simple with SOLR and/or JSONiq. I have tried 
messing around with Facet and Group but they never seem to work the way i want 
them to. For example here is a query i am currently playing with:


?wt=json

&indent=true

&q=*:*

&rows=0

&facet=true

&facet.date=BusinessDateTime

&facet.date.start=2012-02-01T00:00:01Z

&facet.date.end=2013-02-01T23:59:59Z

&facet.date.gap=%2B1MONTH

&group=true

&group.field=BusinessDateTime

&group.facet=true

&group.field=NetSales

Now the facet is working properly however it is returning the count of the 
documents however i need the sum of the NetSales and the TransCount fields 
instead.

Any help or suggestions would be greatly appreciated.

Thanks,
Adam