子表总结和泛型的SQL查询。

I have 4 tables with diagram below

我有4张图表在下面

I want to summary query for the Institution table. where I want to get result of only,

我想对制度表进行总结查询。我想要的结果是，

InstitutionType ProductName Quantity

InstitutionType ProductName数量

For example. sample data of institution table

为例。机构表样本数据

 Id   Name  Address  InstitionTypeId
  1   aaa   ny132    1001
  2   bbb   dx23     1001  
  3   ccc   bn33     1002  

And the InstitionProduct is like that

教唆产品是这样的

     Id  ProductId  Quantity  InstitionId
      1   1000       120       1
      2   1000       100       2
      3   1000       50        3      

Then I want a query result to output total quantity of a given product by Instition Type wise. The sample output will look like this.

然后，我想要一个查询结果来输出给定产品的总数量。示例输出如下所示。

InstitutionTypeId  productId  quantity
1001               1000        220
1002               1000        50 

So I want to group the institution by type and aggregate the product quantity of all institution type group.

所以我想按类型对机构进行分组，并汇总所有机构类型组的产品数量。

I tried to use the group by clause, but with the product quantity not as a grouping element it results in error.

我尝试使用group by子句，但是对于product quantity，它不是作为分组元素，它会导致错误。

2 个解决方案

#1

SELECT
  Institution.InstitutionTypeID,
  InstitutionProduct.ProductID,
  SUM(InstitutionProduct.Quantity)
FROM
  Institution
LEFT JOIN
  InstitutionProduct
    ON InstitutionProduct.InstitutionID = Institution.ID
GROUP BY
  Institution.InstitutionTypeID,
  InstitutionProduct.ProductID

#2

If you are querying with group by you need to use either aggregate functions or group by all included fields. The reason is, that the 'group by' returns exactly one row per 'group by' value, so if you introduce an ungrouped field, this would conflict if the field has more than one value per grouping constraint. Even though this might not be the case for your dataset, the query engine cannot know this, and raises an error.

如果您正在通过group进行查询，则需要使用聚合函数或包含所有字段的group。原因是，“group by”按“value”为每个“group by”返回一行，因此如果引入一个未分组的字段，如果每个分组约束的字段有多个值，那么这将会发生冲突。即使您的数据集可能不是这样，查询引擎也无法知道这一点，并引发错误。

The solution is to introduce aggregates for all non-grouping field with aggregates being (among others): average (avg), summarize (sum), minimum (min) and maximum (max). This would lead to something like

解决方案是为所有非分组字段引入聚合(其中包括):average (avg)、summary (sum)、minimum (min)和maximum (max)。这会导致类似的事情

   SELECT i.InstitutionTypeID, i.Institution.ID, SUM(ip.Quantity) 
   FROM Institution I LEFT JOIN InstitutionProduct IP 
         ON IP.InstituationID = I.ID
   GROUP BY i.InstitutionTypeID, i.Institution.ID