SQL将总和除以另一个值

我正在使用Oracle SQL创建一个视图。

CREATE OR REPLACE VIEW myview AS SELECT 
       [various other columns ommited],
       SUM (mt.Quantity) as "MY_QUANTITY",
       SUM (mt.Quantity) / at.VALUE as "EXPECTED_QUANTITY",
       [more columns ommited]

  FROM MY_TABLE mt LEFT JOIN ANOTHER_TABLE at ON mt.PK = at.FK 
  GROUP BY [various other columns that were ommited]

我遇到的问题是与组织联系的问题。如果我将其删除，则会成功创建视图。我的问题是如何分配这笔钱。我尝试在该行中使用“My_Quantity”别名，而不是总和，但据我所知，这不起作用。我得到的错误是“不是按表达式分组”。

编辑回复评论：如果我将“sum(mt.Quantity)/at.VALUE”添加到我的GROUP BY中，我会得到一个警告“语法错误。部分识别的规则(铁路图)”。如果我尝试添加别名，则会收到“无效标识符”错误

这个问题有些奇怪。如果通过mt主键连接表mt和at，则表mt中只有一行受聚合影响，这意味着mt表中没有什么可以求和的。如果示例数据如下所示：

With
    my_table AS
        (
            Select 1 "MT_PK", 'PK_NAME 1' "PK_NAME", 10 "QUANTITY", 1 "Q_ID" From Dual Union All
            Select 2 "MT_PK", 'PK_NAME 2' "PK_NAME", 12 "QUANTITY", 1 "Q_ID" From Dual Union All
            Select 3 "MT_PK", 'PK_NAME 3' "PK_NAME", 14 "QUANTITY", 2 "Q_ID" From Dual Union All
            Select 4 "MT_PK", 'PK_NAME 4' "PK_NAME", 12 "QUANTITY", 2 "Q_ID" From Dual Union All
            Select 5 "MT_PK", 'PK_NAME 5' "PK_NAME", 10 "QUANTITY", 3 "Q_ID" From Dual Union All
            Select 6 "MT_PK", 'PK_NAME 6' "PK_NAME", 16 "QUANTITY", 3 "Q_ID" From Dual
        ),
    another_table AS
        (
            Select 'A_100' "AT_PK", 1 "FK_MT", 14 "AT_VALUE", 'A' "AT_TYPE" From Dual Union All
            Select 'A_200' "AT_PK", 2 "FK_MT", 15 "AT_VALUE", 'B' "AT_TYPE" From Dual Union All
            Select 'A_300' "AT_PK", 3 "FK_MT", 16 "AT_VALUE", 'C' "AT_TYPE" From Dual
        )

..。如果在(mt.MT_PK=at.FK_MT)上连接表，则使用或不使用SUMMING的SQL都会得到相同的结果

SELECT
    mt.MT_PK "ID",
    mt.PK_NAME "NAME",
    at.AT_TYPE "AT_TYPE",
    Sum(mt.QUANTITY) "QUANTITY",
    Sum(mt.QUANTITY) / at.AT_VALUE  "EXPECTED_VALUE"
FROM
    my_table mt
LEFT JOIN
    another_table at ON(at.FK_MT = mt.MT_PK)
GROUP BY
    mt.MT_PK,
    mt.PK_NAME,
    at.AT_TYPE,
    at.AT_VALUE
ORDER BY 
  mt.MT_PK
--
--  above and below queries results the same  -----
--
SELECT
    mt.MT_PK "ID",
    mt.PK_NAME "NAME",
    at.AT_TYPE "AT_TYPE",
    mt.QUANTITY "QUANTITY",
    mt.QUANTITY / at.AT_VALUE  "EXPECTED_VALUE"
FROM
    my_table mt
LEFT JOIN
    another_table at ON(at.FK_MT = mt.MT_PK)
ORDER BY 
  mt.MT_PK

两者的结果都是..。

/*
        ID NAME      AT_TYPE   QUANTITY EXPECTED_VALUE
---------- --------- ------- ---------- --------------
         1 PK_NAME 1 A               10     .714285714 
         2 PK_NAME 2 B               12             .8 
         3 PK_NAME 3 C               14           .875 
         4 PK_NAME 4                 12                
         5 PK_NAME 5                 10                
         6 PK_NAME 6                 16               

* /

..。如果还有另一个选项来连接示例数据中的表，如Q_ID，那么……

SELECT
    mt.Q_ID "Q_ID",
    at.AT_TYPE "AT_TYPE",
    Sum(mt.QUANTITY) "QUANTITY",
    Sum(mt.QUANTITY) / at.AT_VALUE  "EXPECTED_VALUE"
FROM
    my_table mt
LEFT JOIN
    another_table at ON(at.FK_MT = mt.Q_ID)
GROUP BY
    mt.Q_ID,
    at.AT_TYPE,
    at.AT_VALUE
ORDER BY 
  mt.Q_ID
/*  R e s u l t :
      Q_ID AT_TYPE   QUANTITY EXPECTED_VALUE
---------- ------- ---------- --------------
         1 A               22     1.57142857 
         2 B               26     1.73333333 
         3 C               26          1.625

* /

致敬..。

SQL将总和除以另一个值

1条答案

相关问题

热门标签

最新问答