How to use @Selection with compound SELECT statements?

[wltrup]

Hello there,

I'm trying to use @Selection to put together a complex compound SELECT statement but I'm getting nowhere. I've simplified the problem to a single compound statement for the purpose of this question.

Suppose I have the following struct/table:

@Table
struct KeyValue: Identifiable, Sendable {
   let id: UUID
   let key: Int
   let value: Int
}

and suppose that I want to count the number of instances of a given value for a given key. I can define a struct

@Selection
struct KeyValueCount: Sendable {
   let keyValue: KeyValue
   let count: Int // number of instances of the given value for the given key
}

and a function to fetch all values and their corresponding counts, for a given key:

func fetchKeyValueCounts(for key: Int) -> FetchAll<KeyValueCount> {
   FetchAll(
      KeyValue
         .where { $0.key == key }
         .group(by: \.value)
         .select {
            KeyValueCount.Columns(
               keyValue: $0,
               count: $0.count()
            )
         }
   )
}

So far, so good. But now suppose I also want to summarise all counts. I can define another @Selection struct,

@Selection
struct KeyValueSummary: Sendable {
   let key: Int
   let countOfCounts: Int // number of unique values for the given key
   let totalCount: Int    // sum of all value counts for the given key
   let minCount: Int      // the smallest of all value counts for the given key
   let maxCount: Int      // the largest  of all value counts for the given key
}

but then how do I select from the fetched KeyValueCount rows to obtain the summary? Essentially, I'm trying to execute the query (here for a particular value of the key)

SELECT
   key,
   COUNT(*)   AS countOfCounts,
   SUM(count) AS totalCount,
   MIN(count) AS minCount,
   MAX(count) AS maxCount
FROM (
   SELECT *, COUNT(value) AS count FROM KeyValue
   WHERE key = 100
   GROUP BY value
);

but I can't figure out how to do it using the @Selection macro.

A follow-up question is then how to use structured queries to join the KeyValue table with the KeyValueCount and KeyValueSummary views into the table in order to produce an embellished version of KeyValue that associates a frequency: Double with the count (ie, count / totalCount) and other properties that depend on count and on the summary fields.

Thanks in advance for any and all help.

Edit:
The following is the closest I got to an answer but it still doesn't compile. I understand the error but I don't know how to fix it. I tried using the PointFreeWay docs to help but Xcode isn't seeing them (yes, I followed the instructions to install pfw for Xcode but that's another issue).

func fetchKeyValueSummary(for key: Int) -> FetchOne<KeyValueSummary> {
   FetchOne(
      wrappedValue: fetchKeyValueCounts(for: key)
      .select {    // compiler error here: "Cannot call value of non-function type 'SharedReader<Member>'"
         KeyValueSummary.Columns(
            key: key,
            countOfCounts: $0.count(),
            totalCount: $0.sum(\KeyValueCount.count), // had to add the root type or else the compiler complains
            minCount: $0.min(\KeyValueCount.count),
            maxCount: $0.max(\KeyValueCount.count)
         )
      })
}

Edit:
In desperation, I tried to drop down to sqlite directly and use the #sql macro, but got the same compiler error as above. Again, I understand the error but I just don't know how to fix it.

func fetchKeyValueSummary(for key: Int) -> FetchOne<KeyValueSummary> {
   let subQuery = #sql("""
      ( SELECT *, COUNT(value) AS count
        FROM KeyValue
        WHERE key = \(key) 
        GROUP BY value )
      """)
   let query = #sql("""
      SELECT
         key         AS key,
         COUNT(*)    AS countOfCounts,
         SUM(count)  AS totalCount,
         MIN(count)  AS minCount,
         MAX(count)  AS maxCount
      FROM \(subQuery);
      """)
   return FetchOne(wrappedValue: query)
      .select {
         KeyValueSummary.Columns(
            key: $0.key,
            countOfCounts: $0.countOfCounts,
            totalCount: $0.totalCount,
            minCount: $0.minCount,
            maxCount: $0.maxCount
         )
      }
}

I could really use someone's help with this. I've already spent two full days trying to make this work and I'm now considering not using sqlite-data at all because I can't afford to be stuck much longer on what I suspect is a simple problem with a simple solution, which I can't see because of a basic misunderstanding on my part.

Thank you all for any help.