Oh, right, sorry. The following should work. It duplicates the value column as a new media_title_str column and then groups by that column. That way it can store the count in the _value column:
from(bucket: "home_assistant")
|> range(start: v.timeRangeStart, stop: v.timeRangeStop)
|> filter(fn: (r) => r["_measurement"] == "media_player.spotify_i")
|> filter(fn: (r) => r["domain"] == "media_player")
|> filter(fn: (r) => r["_field"] == "media_title_str")
|> filter(fn: (r) => r["friendly_name"] == "Spotify i")
|> filter(fn: (r) => r["entity_id"] == "spotify_i")
|> duplicate(column: "_value", as: "media_title_str")
|> group(columns: ["media_title_str"])
|> count()
|> group()
|> top(n: 10)
Looking at the timestamps of the two fields (media_duration and state), it appears these are collected at different intervals. media_duration seems to be an “event” that emits a point whenever something happens and the activity interval is inconsistent. state appears to be a “metric” that is reported every 10s and isn’t tied to a specific event. The reason I say this is, typically, you could join these datasets based on the time, but state and media_duration don’t have common time values and there are no other identifying tags that you could join on. Unfortunately, I don’t know that you can do what you’re trying to do with the data as it is.
Do you have control over the schema of the data and how it’s ingested?
