如何在Oracle中的条件下使用Case计算多列的总和 [英] How to calculate sum of multi column with Case when condition in Oracle
问题描述
我正在使用 oracle SQL 计算我的数据库中某些列的值的总和，并在我的条件下使用案例
I'm using oracle SQL to calculate sum of value some columns in my database with case when in my condition
这是我的带有值的 LOG0104M 表:
this is my LOG0104M table with values:
PRODUCT_CODE PRODUCT_NAME PACKING_STYLE TOTAL_QUANTITY QUANTITY_IN_FULL_CTN SPAREBOX
3300099 AP-1516D 30 50 1 NULL
3330000 NEOSENSE 60 20 1 NULL
使用上面的列值，我想计算 'SPAREBOX' 的输出值是 0 或 1.要输出 'SPAREBOX' 的值是 0 或 1，它将基于 Spare_quantity 的结果与此配方:
with value of columns above, i want to calculate output value of 'SPAREBOX' is 0 OR 1. To output value of 'SPAREBOX' is 0 OR 1, it will base on result of Spare_quantity with this recipe:
1. FullBox = (Total_Quantity / Packing_Style)
2. Spare_quantity = Total_Quantity - FullBox * Packing_Style.
If Spare_quantity = 0 => SpareBox column = 0
If Spare_quantity > 0 => SpareBox column = 1.
示例:
1. 50 / 30 = FullBox (1,7)
2. 50 - 1.7 * 30 = Spare_quantity( 1,5)
=> Spare_quantity > 0 and then SpareBox column = 1
预期结果:
PRODUCT_CODE PRODUCT_NAME PACKING_STYLE TOTAL_QUANTITY QUANTITY_IN_FULL_CTN SPAREBOX
3300099 AP-1516D 30 50 1 1
这是我的查询:
WITH BOXCOUNT AS (
SELECT ROUND(SUM(TOTAL_QUANTITY/PACKING_STYLE)) AS FULLBOX FROM LOG0104M)
SELECT
L55.PRODUCT_CODE
, L55.PRODUCT_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, L55.SPAREBOX
, BC.FULLBOX
,CASE
WHEN SUM(L55.TOTAL_QUANTITY - BC.FULLBOX * L55.PACKING_STYLE) = 0 THEN L55.SPAREBOX = 0
WHEN SUM(L55.TOTAL_QUANTITY - BC.FULLBOX * L55.PACKING_STYLE) != 0 THEN L55.SPAREBOX = 1
END AS SPARE_QUANTITY
FROM LOG0104M L55, BOXCOUNT BC
GROUP BY
L55.PRODUCT_CODE
, L55.PRODUCT_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, BC.FULLBOX
, L55.SPAREBOX
它似乎以错误的结果运行，而不是预期的结果.
it seem run with wrong result and not as expected result.
如何解决问题?非常感谢
How to fix the problem ? many thank
推荐答案
你还没有提供太多的例子.如果您想要一个严格的答案，您需要包含足够的示例数据以包含您想要的所有行为.
You still haven't provided much is the way of example. If you want a rigorous answer you need to include enough example data to include all the behaviour you desire.
例如，为什么您的示例输入有两行，而您想要的结果只有一行?
For example, why does your example input have two rows, but your desired results have one row?
拼命地试图在两行之间阅读我认为您想要的只是每行多出一列，说明该行是否完全填满了它的框?
Desperately trying to read between the lines I think that all you want is an extra column on each row, saying if that row has perfectly filled its boxes or not?
这只是一个使用 MOD()...
That would simply be a CASE expression using MOD()...
SELECT
L55.PRODUCT_CODE
, L55.PRODUCT_NAME
, L55.TOTAL_QUANTITY
, L55.PACKING_STYLE
, L55.QUANTITY_IN_FULL_CTN
, L55.SPAREBOX
, CASE WHEN MOD(L55.TOTAL_QUANTITY, L55.PACKING_STYLE) = 0 THEN 0 ELSE 1 END AS SPARE_QUANTITY
FROM
LOG0104M L55
如果这不是您想要的，请添加一组完整输入数据和一组完整与该输入对应的结果，展示正是您需要的.
If that's not what you want, please add a full set of input data and a full set of results that correspond to that input, demonstrating exactly what you need.
如果您不确定我的意思，请参考我之前给您的链接.
If you're uncertain about what I mean, please refer to the links I gave you previously.
这篇关于如何在Oracle中的条件下使用Case计算多列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
