Dataview: Statistical counts, multiple values in metadata

[anightbutterfly]

What I’m trying to do

While moving my film base from Letterboxd to Obsidian I came across the idea to create similar stats. And I’m quite satified with my results. However, I would like a dataview table for my most seen countries. I’ve worked it out in a way that it shows the individual countries and the counter. However, I’d like to have a row with ‘cooperation’ in case that there is more the one country (all individual values) in the meta data field country.

Examples of my meta data fields:
More than one country:
One country:

Things I have tried

So far I’ve just used this one:

table WITHOUT ID
countr as "Meistgesehene Länder", 
length(rows) as "Anzahl"
from "2 - Quellen/4 - Filme und kurze Serien"
Where contains(status, "beendet")
where country
flatten country as countr
group by countr
sort length(rows) desc
LIMIT 15

Which gives me this:

And I don’t know how to integrate row with ‘Cooperation’

I’ve also tried a solution with dataviewjs:

// Get all pages with a status of "beendet"
const pages = dv.pages('"2 - Quellen/4 - Filme und kurze Serien"')
  .where(p => p.status && p.status.includes("beendet"));

// Initialize a map to store the countries and their counts
let countryCounts = new Map();

// Iterate over the pages to count countries
pages.forEach(p => {
  if (p.country) {
    // If "country" is an array (multiple countries), process it accordingly
    const countries = Array.isArray(p.country) ? p.country : [p.country];
    
    // If multiple countries are listed, mark it as "Kooperation"
    if (countries.length > 1) {
      countries.forEach(country => {
        countryCounts.set("Kooperation", (countryCounts.get("Kooperation") || 0) + 1);
      });
    } else {
      // If only one country, count it individually
      countries.forEach(country => {
        countryCounts.set(country, (countryCounts.get(country) || 0) + 1);
      });
    }
  }
});

// Convert the map to an array and sort by count (the second item in the entry) in descending order
let countryData = Array.from(countryCounts.entries())
  .sort(([countryA, countA], [countryB, countB]) => countB - countA);  // Sort by count descending

// Limit the countries to a maximum of 14 (keeping "Kooperation" at the top)
if (countryData.length > 15) {
  countryData = countryData.slice(0, 15);  // First element is "Kooperation", so 14 more countries
}

// Create the table with countries and their counts
dv.table(["Meistgesehenes Länder", "Anzahl"], countryData);

Which kind of gives me what I want. However, the numbers are wrong. Because I have 216 film notes in the obsidian folder and the numbers don’t add up.

So if anyone has any ideas, I’d be super happy and grateful (●’◡’●)