CODEWITHSUNDEEP
solrfacetsolr6
JHS
JHS

Reputation: 7871

Solr 6 - SUM values of a field along with GROUP BY

I have a Solr core having documents with 6 fields as below -

FIELDNAME   TYPE    INDEXED   STORED
department  STRING  TRUE      TRUE
group       STRING  TRUE      TRUE
age         INT     TRUE      TRUE
salary      INT     TRUE      TRUE
bonus       INT     TRUE      TRUE

What I want is the following -

  1. SUM the salary of all employees from different department.
  2. SUM the salary and bonus of all employees from different department.
  3. SUM the salary of all employees from different department and group.
  4. SUM the salary and bonus of all employees from different department and group.

Example data -

<doc>
  <str name="department">IT</str>
  <str name="group">INFRASTRUCTURE</str>
  <int name="age">27</int>
  <int name="salary">1000</int>
  <int name="bonus">10</int>
</doc>
<doc>
  <str name="department">IT</str>
  <str name="group">DEVELOPMENT</str>
  <int name="age">30</int>
  <int name="salary">10000</int>
  <int name="bonus">100</int>
</doc>
<doc>
  <str name="department">IT</str>
  <str name="group">DEVOPS</str>
  <int name="age">32</int>
  <int name="salary">2000</int>
  <int name="bonus">150</int>
</doc>
<doc>
  <str name="department">IT</str>
  <str name="group">INFRASTRUCTURE</str>
  <int name="age">35</int>
  <int name="salary">20000</int>
  <int name="bonus">200</int>
</doc>
<doc>
  <str name="department">HR</str>
  <str name="group">PEOPLE</str>
  <int name="age">27</int>
  <int name="salary">900</int>
  <int name="bonus">5</int>
</doc>

Excepted Output -

1. For the 1st requirement -
   IT - 33000
   HR - 900
2. For the 2nd requirement -
   IT - 33460
   HR - 905
3. For the 3rd requirement -
   IT -
      INFRASTRUCTURE - 21000
      DEVELOPMENT - 10000
      DEVOPS - 2000
   HR -
      PEOPLE - 900
4. For the 4th requirement -
   IT -
      INFRASTRUCTURE - 21210
      DEVELOPMENT - 10100
      DEVOPS - 2150
   HR -
      PEOPLE - 905

I tried to achieve it by following the directions here, however, I am unable to merge PIVOT FACET with the SUM function.

Upvotes: 3

Views: 2595

Answers (1)

Persimmonium
Persimmonium

Reputation: 15791

this should work, untested, might have some typo etc...

curl http://localhost:8983/solr/col/query -d '
  q=*:*&
  json.facet={
   departments:{
    type : terms,
    field : department,
    facet:{
      sumsalary : "sum(salary)",
      sumbonus : "sum(bonus)",
      groups:{
        type : terms,
        field : group,
        facet:{
           sumsalaryg : "sum(salary)",
           sumbonusg : "sum(bonus)"
       }
    }
  }
}'

Upvotes: 3

Related Questions