How to COUNT(DISTINCT) a certain object key together with SUM and GROUP BY based on other keys?

Question

I have this array of JSON objects :

let merged = [
    {
      user_id: 2,
      year: 2021,
      month: 'July',
      gross_subscription_amount: 650,
      gross_redemption_amount: 0,
      gender: 'M',
      state: '01',
      age: '18',
      number_of_investors: 0
    },
    {
      user_id: 1,
      year: 2026,
      month: 'October',
      gross_subscription_amount: 0,
      gross_redemption_amount: 10,
      gender: 'M',
      state: '01',
      age: '20',
      number_of_investors: 0
    },
    {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
    {
        user_id: 3,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
      {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      }
]

I would like to do an equivalent of this SQL Query:

SELECT year, month, gender, state, age, 
SUM(gross_subscription_amount), 
SUM(gross_redemption_amount), 
COUNT(DISTINCT user_id)
GROUP BY year, month, gender, state, age

where each user (seen from their 'user_id') are grouped based on a combination of these categories:

year, month, gender, stage, age (in this order)

and get a sum of their 'gross_subscription_amount' and 'gross_redemption_amount' .

My issue here is I am unable to execute the COUNT(DISTINCT) part.

The code I tried is below:

let merged = [
    {
      user_id: 2,
      year: 2021,
      month: 'July',
      gross_subscription_amount: 650,
      gross_redemption_amount: 0,
      gender: 'M',
      state: '01',
      age: '18',
      number_of_investors: 0
    },
    {
      user_id: 1,
      year: 2026,
      month: 'October',
      gross_subscription_amount: 0,
      gross_redemption_amount: 10,
      gender: 'M',
      state: '01',
      age: '20',
      number_of_investors: 0
    },
    {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
    {
        user_id: 3,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
      {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      }
]


var helper = {};
var result = merged.reduce(function(r, o) {
  var key = o.year + '-' + o.month + '-' + o.gender + '-' + o.age + '-'+ o.state;
  
  if(!helper[key]) {
    helper[key] = Object.assign({}, o); // create a copy of o
    r.push(helper[key]);
  } else {
    helper[key].gross_subscription_amount += o.gross_subscription_amount;
    helper[key].gross_redemption_amount += o.gross_redemption_amount;
    helper[key].number_of_investors++;
  }

  return r;
}, []);

console.log(result);

From the result, the first group:

Year - 2021 Month- July Gender - M State - 01 Age - 18

There are supposed to be 2 unique users in this group (user_id: 2 & 3) but it is returning the wrong number of investors value. Expected Result is below:

[
  {
    "year": 2021,
    "month": "July",
    "gross_subscription_amount": 887,
    "gross_redemption_amount": 0,
    "gender": "M",
    "state": "01",
    "age": "18",
    "number_of_investors": 2
  },
  {
    "year": 2026,
    "month": "October",
    "gross_subscription_amount": 0,
    "gross_redemption_amount": 10,
    "gender": "M",
    "state": "01",
    "age": "20",
    "number_of_investors": 0
  }
]

Thanks in advance.

There's no [JSON](https://www.json.org/json-en.html) in your question -> [What is the difference between JSON and Object Literal Notation?](https://stackoverflow.com/questions/2904131/) — Andreas, Jul 17 '21 at 15:40
it's returning the wrong number of investors because it's returning user_id 2 and 1, not 2 and 3 — The Bomb Squad, Jul 17 '21 at 15:52
@TheBombSquad Sorry for the confusion, its returning user_id 2 and 1 because user_id 2 is one group (containing the sum of values of user_id 2 and 3) and user_id 1 is a whole different group. I'm not sure if it affects the calculation of number_of_investors as it is not even supposed to be there in the first place. I am not sure how to remove it. — BreadBoard.ini, Jul 17 '21 at 15:56
so for each part that has the same user_id are you summing them or are you taking out duplicates? — The Bomb Squad, Jul 17 '21 at 16:04
ok.. one question.. there are 3 unique users.. so why only show 2? — The Bomb Squad, Jul 17 '21 at 16:30
@TheBombSquad I've commented to your answer below, could you check if that clarifies it? I've posted the expected result above as well. — BreadBoard.ini, Jul 17 '21 at 16:33

The Bomb Squad · Accepted Answer · 2021-07-17T17:03:21.023

I'm not exactly sure what you want but if you want to search through an array that has only UNIQUE user_id, a simple filter will do

Upon looking at your code I'm making a guess that you want to only show instances of unique user_id and for instances that are not unique.. you sum gross_subscription_amount and gross_redemption_amount into those unique instances

EDIT well I saw you say this

Yes 3 unique users, user id 1, 2, and 3. But user id 2 and 3 has the same Year, Month, Gender, State, Age. This counts as 1 thing. Then remaining is user id 1, which counts as another thing

var record = {};
var result = merged.map(part=>{return {...part}}) //to not edit things in the variable "merged"
.filter(part=>{
  //long repeated strings
  let sub="gross_subscription_amount"
  let red="gross_redemption_amount"
  let inv="number_of_investors"
  let {year,month,gender,age,state}=part
  let key=`${year}-${month}-${gender}-${age}-${state}`
  
  var exists=record[part.user_id] || record[key] //to check for if part is unique(in terms of user_id AND key)
  if(exists){ //addition to reference of unique instance
    exists[sub] += part[sub]
    exists[red] += part[red]
    if(record[part.user_id]==exists){exists[inv]++} //COUNT if unique user_id
  }
  else{ //record holds references of unique instances
    record[part.user_id]=part //id storage
    record[key]=part //key storage
    delete(part.user_id) //your expected answer excludes the user_id element
    part[inv]=0 //setting the COUNT(funny thing.. sql count returns the amount-1 xD)
    return true
  }
}); //array of summed unique instances(edited smoothly due to reference logic)

console.log(result);

<script>
//just putting this below since it's big and the important part is above
window.merged = [
    {
      user_id: 2,
      year: 2021,
      month: 'July',
      gross_subscription_amount: 650,
      gross_redemption_amount: 0,
      gender: 'M',
      state: '01',
      age: '18',
      number_of_investors: 0
    },
    {
      user_id: 1,
      year: 2026,
      month: 'October',
      gross_subscription_amount: 0,
      gross_redemption_amount: 10,
      gender: 'M',
      state: '01',
      age: '20',
      number_of_investors: 0
    },
    {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
    {
        user_id: 3,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      },
      {
        user_id: 2,
        year: 2021,
        month: 'July',
        gross_subscription_amount: 79,
        gross_redemption_amount: 0,
        gender: 'M',
        state: '01',
        age: '18',
        number_of_investors: 0
      }
]
</script>

@Thank you for the answer, I've edited my question and posted the expected result above. This is similar to what I'm looking for however I would like to group this even more. From the result of your code, user_id 2 and 3 has the same value for every key, hence I'd like to group all users with the same key values and SUM their "gross_sub_amount" as well as "gross_redemption_amount", and get the correct "number_of_investors", in this case 2 investors (user_id 2 & 3). — BreadBoard.ini, Jul 17 '21 at 16:31
@BreadBoard.ini what are you trying to do with `number_of_investors`? because every instance in `merged` starts at `0` and i see the expected result.. going with numbers more than 0... OHHH is it the count of how many times `user_id` exists - 1? — The Bomb Squad, Jul 17 '21 at 16:34
number_of_investors indicate how many users there are in a group. In my expected result above, each object in the array is one group, the first group has 2 investors, the second group has 1 investors — BreadBoard.ini, Jul 17 '21 at 16:36
yup im still lost as to why 2 things and not 3.. thinking........... — The Bomb Squad, Jul 17 '21 at 16:37
Thx for your help, 2 things because in my expected result it returns 2 groups. 1st group is: Year - 2021 Month - July Gender - M State - 01 Age - 18 and this group has 2 users that fit that has the above year, month, state, age — BreadBoard.ini, Jul 17 '21 at 16:42
ok.. let me explain why I'm confused as to why it's 2 things.. **because there are `3` unique users** — The Bomb Squad, Jul 17 '21 at 16:44
Yes 3 unique users, user id 1, 2, and 3. But user id 2 and 3 has the same Year, Month, Gender, State, Age. This counts as 1 thing. Then remaining is user id 1, which counts as another thing — BreadBoard.ini, Jul 17 '21 at 16:47
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/235009/discussion-between-breadboard-ini-and-the-bomb-squad). — BreadBoard.ini, Jul 17 '21 at 16:50
Thank you for the help and step by step breakdown to the solution! This is working as intended! — BreadBoard.ini, Jul 18 '21 at 05:28

How to COUNT(DISTINCT) a certain object key together with SUM and GROUP BY based on other keys?

1 Answers1