Hi Adam, Have a look at the stats component: http://wiki.apache.org/solr/StatsComponent. In your case, I think you'd need to add an extra field for your month, and then run a query filtered by your date range with stats.field=NetSales, stats.field=TransCount, and stats.facet=month.
Make sure you use Solr 4.2 for this, by the way, as it's massively faster - I've found stats queries over ~500,000 documents dropping from 60 seconds to 2 seconds with an upgrade from 4.0 to 4.2. Alan Woodward www.flax.co.uk On 18 Mar 2013, at 16:48, Adam Harris wrote: > 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
