サンプルデータを用意して結合・サブクエリを使った集計を試します。

いつものようにどこかの焼き鳥屋さんの注文と注文明細のようなサンプルデータを用意しました。システムによって生成されるプロパティは省略しています。

この注文データに対してid別の注文合計金額（details.price * details.quantityのsum）を取得してみたいと思います。

[
  {
    "id": "1",
    "details": [
      {
        "menu": "純けい",
        "price": 360,
        "quantity": 3
      },
      {
        "menu": "しろ",
        "price": 330,
        "quantity": 2
      }
    ]
  },
  {
    "id": "2",
    "details": [
      {
        "menu": "若皮",
        "price": 330,
        "quantity": 2
      }
    ]
  }
]

結合（JOIN）を使ったクエリで集計する

集計する前にINキーワードを使ったJOINの結果を確認します。 結果はクロス積になるのでdetailsが平坦化されます。detailsの項目1つ1つにidが付与された結果になっていることがわかります。

クエリ：
select
  o.id,
  --o.details,
  d.menu,
  d.price,
  d.quantity
from o
  join d in o.details
結果：
[
  {
    "id": "1",
    "menu": "純けい",
    "price": 360,
    "quantity": 3
  },
  {
    "id": "1",
    "menu": "しろ",
    "price": 330,
    "quantity": 2
  },
  {
    "id": "2",
    "menu": "若皮",
    "price": 330,
    "quantity": 2
  }
]

このクエリにグルーピングと集計関数を使うと、求める合計を取得できます。

クエリ：
select
  o.id,
  sum(d.price * d.quantity) as total
from o
  join d in o.details
--order by o.id
group by o.id
結果：
[
  {
    "id": "2",
    "total": 660
  },
  {
    "id": "1",
    "total": 1740
  }
]

id順で並び替えをしようとして上記クエリにorder by句を足したらエラーになりました。並び替えたい場合は次のようにサブクエリにする必要があるのかもしれません。

select *
from (
  select
    o.id,
    sum(d.price * d.quantity) as total
  from o
    join d in o.details
  group by o.id
)

サブクエリを使って集計する

サブクエリを使って合計を集計するクエリです。このクエリでも同じ結果を取得できました。

クエリ：
select
  o.id,
  (select value sum(d.price * d.quantity) from d in o.details) as total
from o
order by o.id
結果：
[
  {
    "id": "1",
    "total": 1740
  },
  {
    "id": "2",
    "total": 660
  }
]

参考