I have 3 columns: date, user & total with multiple rows for the same date. I am using the query function to derive date wise total and unique count of the names but i am not able to count the unique occurence of values in user column.

Please refer to the attached google sheet and image of the sheet for sample data and a better understanding of my question.

Thanks for looking.

https://docs.google.com/spreadsheets/d/1FeNhOD_oCSOKGXAhQ0EVDqIUOgP3DRzZtMuUI6eHCuw/edit?usp=sharing

[Image of spreadsheet]

CodePudding user response：

Try this:

function myfunk() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const vs = sh.getRange(3,1,sh.getLastRow() - 2,3).getDisplayValues();
  const osh = ss.getSheetByName("Sheet1");
  osh.clearContents();
  let obj = {pA:[]};
  vs.forEach((r,i) => {
    if(!obj.hasOwnProperty(r[0])) {
      obj[r[0]] = {user:[r[1]],sum:Number(r[2])};
      obj.pA.push(r[0]);
    } else {
      obj[r[0]].user.push(r[1]);
      obj[r[0]].user = [...new Set(obj[r[0]].user)];
      obj[r[0]].sum  = Number(r[2])
    }
  }); 
  let o = [["Date","sumTotal","unique user count"]];
  obj.pA.forEach(p => {
    o.push([p,obj[p].user.length,obj[p].sum]);
  });
  osh.getRange(1,1,o.length,o[0].length).setValues(o);
}

Data:

Output:

CodePudding user response：

Use double query, first to group by and get unique and second to aggregate:

=QUERY(QUERY(A1:C15,"Select A,count(B),sum(C) group by A,B"),"Select Col1,count(Col2),sum(Col3) group by Col1",1)