Alan - Thank you, with that information I can manage to get the correct data from SOLR and then aggregate it in JSONiq.
Eric / Walter - I totally agree that SOLR is more than likely not the solution we should be using however all I can do is hope that upper management comes to their senses and realizes this. All - Thank you all for your help and suggestions. It's greatly appreciated. Thanks, Adam -----Original Message----- From: Erick Erickson [mailto:erickerick...@gmail.com] Sent: Monday, March 18, 2013 8:56 PM To: solr-user@lucene.apache.org Subject: Re: Group By and Sum Second Walter's comment. Make really, _really_ sure that "the powers that be" recognize that they're asking for something unreasonable and it'll cost them dearly to get it. Best Erick On Mon, Mar 18, 2013 at 12:04 PM, Alan Woodward <a...@flax.co.uk> wrote: > 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 > >
