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

vwhgwdsa  于 2023-04-11  发布在  Oracle
关注(0)|答案(2)|浏览(180)

我在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,它被运到的国家,发货人名称,订单总额和收取的运费总额。

提问

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

6bc51xsx

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"
   ...

不管用吗

91zkwejq

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子句中未分组的表达式完全匹配。按实际值分组就足够了-您仍然可以在选择中对它们应用函数。

相关问题