lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adam Harris <ahar...@marketforce.com>
Subject Group By and Sum
Date Mon, 18 Mar 2013 16:48:41 GMT
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

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message