Dang. Well, make your estimates clear. I would not be surprised if this took a few weeks to get something that worked but was too slow. It might require new Solr or Lucene features to make it fast.
It may be possible to put something together out of the existing features. If it is, the people on this list are the ones who could make that hack work. wunder On Mar 18, 2013, at 10:13 AM, Adam Harris wrote: > I agree however the powers that be, being upper management, have decided that > we need to switch to SOLR, JSONiq and JavaScript MVC for all our reporting > needs. I would love to just keep using the SQL DB that we have been using but > alas I am not allowed to. > > Thanks, > Adam > > -----Original Message----- > From: Walter Underwood [mailto:wun...@wunderwood.org] > Sent: Monday, March 18, 2013 11:58 AM > To: solr-user@lucene.apache.org > Subject: Re: Group By and Sum > > You should use a relational database. Solr is not really designed for this > kind of query. > > wunder > > On Mar 18, 2013, at 9:48 AM, 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 > > -- > Walter Underwood > wun...@wunderwood.org > > > -- Walter Underwood wun...@wunderwood.org
