DQL query with two values for same field in one note

[RenanSantos7]

In my daily notes, I record the game I played. So, I’ve created this query to count the days I’ve played each game:

TABLE length(rows) as "Dias Jogados"
FROM "Experiências/Diário"
WHERE jogo
GROUP BY jogo as Jogo

The problem

When a note has two values for the jogo (game, in portuguese) field, they are counted as being a different value. For example, if in a note I put this:

jogo:: Genshin Impact
jogo:: Minecraft

… this note is not counted as +1 in the “Minecraft” row and +1 in the “Genshin Impact” row, but as one “- Minecraft - Genshin Impact” separated row, like the screenshot:

So, in “Genshin Impact” row, might be 6 on the second column instead 4.

How to resolve this?

[mnvwvnm]

@RenanSantos7

TABLE length(rows) as "Dias Jogados"
FROM "Experiências/Diário"
WHERE jogo
GROUP BY jogo as Jogo

This query works fine for single values in the field jogo. But for multiples values you need to try other approach: you need to use the flatten command.

(agora em português).

Experimenta esta opção:

TABLE length(rows) as "Dias Jogados"
FROM "Experiências/Diário"
WHERE jogo
FLATTEN jogo AS Jogo
GROUP BY Jogo
SORT length(rows) DESC

O sort é opcional…

[RenanSantos7]

Thank you!

I’d never understand FLATTEN command, so I was lost about it.