在Oracle中查找列之间的百分比

vwhgwdsa 于 2023-04-11 发布在 Oracle

关注(0)|答案(2)|浏览(181)

我在Oracle中写了一段SQL代码：

SELECT DISTINCT 
          O.shipcountry, S.companyname,
          O.orderid,
          TO_CHAR(O.freight, '$999,999.999'), 
          TO_CHAR(sum(unitprice)*count(quantity), '$999,999.99') as "Order Total"
     FROM corp.Order_Details D
LEFT JOIN corp.Orders O ON D.orderid = O.orderid)
LEFT JOIN corp.Shippers S ON O.shipvia = S.Shipperid
    WHERE O.freight >= '500'
 GROUP BY O.shipcountry, S.companyname, O.orderid, to_char(O.freight, '$999,999.999');

这基本上给了我一个订单ID，它被运到的国家，发货人名称，订单总额和收取的运费总额。

提问

在不使这太复杂的情况下，有谁知道我如何写运费的百分比（运费/订单总额）？我试着把它放进去，但它说它不能分组，因为订单总额中的总和。

oracle

来源：https://stackoverflow.com/questions/3895987/finding-percentage-between-columns-in-oracle

2条答案

按热度按时间

6bc51xsx1#

所以...

SELECT DISTINCT 
       O.shipcountry, 
       S.companyname, 
       O.orderid,
       TO_CHAR(O.freight, '$999,999.999'), 
       TO_CHAR(SUM(unitprice) * COUNT(quantity), '$999,999.99') as "Order Total", 
       O.freight / (SUM(unitprice) * COUNT(quantity)) as "Percent"
   ...

不管用吗

赞(0）回复(0）举报 2023-04-11

91zkwejq2#

我假设你的意思是你添加了类似O.freight / (SUM(unitprice) * COUNT(quantity))的东西，结果得到了ORA-00979: not a GROUP BY expression。
这样做的原因是O.freight不是GROUP BY表达式中的一个--您是按to_char(O.freight, '$999,999.999')分组的。您应该能够单独按列值分组并使其工作。

SELECT DISTINCT 
          O.shipcountry, S.companyname,
          O.orderid,
          TO_CHAR(O.freight, '$999,999.999'), 
          TO_CHAR(sum(unitprice)*count(quantity), '$999,999.99') as "Order Total",
          O.freight / (SUM(unitprice) * COUNT(quantity)) as "Percent"
     FROM corp.Order_Details D
LEFT JOIN corp.Orders O ON D.orderid = O.orderid)
LEFT JOIN corp.Shippers S ON O.shipvia = S.Shipperid
    WHERE O.freight >= '500'
 GROUP BY O.shipcountry, S.companyname, O.orderid, O.freight;

我怀疑这是一个常见的误解，即GROUP BY子句中的表达式必须与SELECT子句中未分组的表达式完全匹配。按实际值分组就足够了-您仍然可以在选择中对它们应用函数。

赞(0）回复(0）举报 2023-04-11

我来回答

在Oracle中查找列之间的百分比

提问

2条答案

相关问题

热门标签

最新问答