3

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.

JHS
  • 7,761
  • 2
  • 29
  • 53

1 Answers1

3

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)"
       }
    }
  }
}'
Persimmonium
  • 15,593
  • 11
  • 47
  • 78
  • Thank you for the answer. I am also looking to add the values of `salary` and `bonus` into one value within the group. Can this be achieved? OR I have to add the 2 separately? The real scenario has many fields and sometimes the total of all the fields is required. – JHS May 02 '17 at 10:13
  • just try it: sum(salary, bonus) the sum FunctionQuery allows multiple inputs, but not sure this one does. – Persimmonium May 02 '17 at 11:46
  • Does not work. Get an error `org.apache.solr.search.SyntaxError: Expected ')' at position 17 in 'sum(salary,bonus)'` – JHS May 02 '17 at 11:57
  • then, yes, you need to add them on the client side. Or, you add a field salaryPlusBonus that you can sum() on – Persimmonium May 02 '17 at 13:05