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 > >
