Results in many tables instead of many rows in a single table

Hi all :wave:

I have a query that gives me a result of many tables instead of a single table with many rows and I would like some guidance in how to address this, and maybe a brief explain why I see this behavior in the first place. Being more verse with SQL, the result is not what I would expect to see.


from(bucket: "cust-csg")
  |> range(start: 2021-06-01T00:00:00Z, stop: 2021-06-30T23:59:59Z)
  |> filter(fn: (r) => r["_measurement"] == "MessageTrace")
  |> filter(fn: (r) => r["_field"] == "RecipientCountDelivered")
  |> group(columns: ["UserPrincipalName"])
  |> aggregateWindow(every: 30d, fn: sum, createEmpty: false)
  |> drop(columns: ["_time", "_start", "_stop"])
  |> rename(columns: {_value: "June"})

Expected result

UserPrincipalName June
sales1@… 104
sales2@… 39

Actual result

As a bonus, is there any date modifier that could more easily give me the range of a given month? E.g. input of ‘2021-06’ would result in a range between 2021-06-01T00:00:00Z and 2021-06-30T23:59:59Z.

Thanks everyone!

I think a final |> group(columns: []) statement do just that.

Maybe can use pivot function.

Yep, should just need this on the end

  |> group(columns: ["_time", "_start", "_stop"])