lucene-solr-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Adam Harris <ahar...@marketforce.com>
Subject RE: Group By and Sum
Date Mon, 18 Mar 2013 17:13:19 GMT
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:wunder@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
wunder@wunderwood.org




Mime
View raw message