我有以下 SQL,我试图在其中获得总计数的一个月内的每日平均值。
SELECT A.BUSINESS_UNIT, A.INV_ITEM_ID, C.MFG_ID, C.MFG_ITM_ID,
A.ADJUST_TYPE, A.QTY_BASE, A.UNIT_MEASURE_STD,
A.DT_TIMESTAMP, B.DESCR254_MIXED, A.TRANSACTION_GROUP,
(CONVERT(CHAR(10),A.TRANSACTION_DATE,121)), A.DEPTID, B.PRICE_LIST,
A.DESTIN_GL_BU, A.QTY_BASE* B.PRICE_LIST, (COUNT(*) * 1.0 / 31 ) AS 'Daily
Average'
FROM PS_TRANSACTION_INV A, (PS_PURCH_ITEM_ATTR B LEFT OUTER JOIN
PS_ITEM_MFG C ON B.SETID = C.SETID AND B.INV_ITEM_ID = C.INV_ITEM_ID ),
PS_CM_ACCTG_DIST_D D
WHERE ( A.BUSINESS_UNIT = '11MMS'
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND C.PREFERRED_MFG = 'Y'
AND A.TRANSACTION_DATE >= '05-01-2018'
AND A.TRANSACTION_DATE <= '05-31-2018'
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.INV_ITEM_ID = D.INV_ITEM_ID
AND D.TRANSACTION_GROUP = A.TRANSACTION_GROUP
AND A.INV_ITEM_ID = '1')
GROUP BY A.BUSINESS_UNIT, A.INV_ITEM_ID, C.MFG_ID, C.MFG_ITM_ID,
A.ADJUST_TYPE, A.QTY_BASE, A.UNIT_MEASURE_STD,
A.DT_TIMESTAMP, B.DESCR254_MIXED, A.TRANSACTION_GROUP,
(CONVERT(CHAR(10),A.TRANSACTION_DATE,121)), A.DEPTID, B.PRICE_LIST,
A.DESTIN_GL_BU, A.QTY_BASE* B.PRICE_LIST
使用上面运行的数据,我得到 36 行(我的计数)的输出,我将其除以 31(天),并期望每日平均值为 1.161,但是我的每日平均值的每一行输出为 0.032258(我确定为 1.16 / 36)。非聚合列在 Group By 中,但以某种方式扭曲了每日平均值。
如果我只对计算出的平均值运行查询,(COUNT(*) * 1.0 / 31 ) AS 'Daily Average'
那么我会得到正确的每日平均值 1.161,所以我知道它与分组依据有关。提前致谢!
编辑:
运行此查询会产生 1.161 的正确结果,只是当我将它添加到上面的查询时, Group By 是我的结果不正确。这就是我试图在主查询中模仿的内容。SELECT (COUNT(*) * 1.0 / 31 ) AS 'Daily Average' FROM PS_TRANSACTION_INV A, (PS_PURCH_ITEM_ATTR B LEFT OUTER JOIN
PS_ITEM_MFG C ON B.SETID = C.SETID AND B.INV_ITEM_ID = C.INV_ITEM_ID ), PS_CM_ACCTG_DIST_D D WHERE ( A.BUSINESS_UNIT = '11MMS' AND A.INV_ITEM_ID = B.INV_ITEM_ID AND C.PREFERRED_MFG = 'Y' AND A.TRANSACTION_DATE >= '05-01-2018' AND A.TRANSACTION_DATE <= '05-31-2018' AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.INV_ITEM_ID = D.INV_ITEM_ID AND D.TRANSACTION_GROUP = A.TRANSACTION_GROUP AND A.INV_ITEM_ID = '1')
当您按所有列添加 group 时,count 的结果将始终为 1,因此您得到的结果。
执行您要求的最简单方法是将第二个查询添加为主查询的子查询,届时您可能会丢失该组,如下所示:
SELECT A.BUSINESS_UNIT, A.INV_ITEM_ID, C.MFG_ID, C.MFG_ITM_ID,
A.ADJUST_TYPE, A.QTY_BASE, A.UNIT_MEASURE_STD,
A.DT_TIMESTAMP, B.DESCR254_MIXED, A.TRANSACTION_GROUP,
(CONVERT(CHAR(10),A.TRANSACTION_DATE,121)), A.DEPTID, B.PRICE_LIST,
A.DESTIN_GL_BU, A.QTY_BASE* B.PRICE_LIST,
(SELECT COUNT(*) * 1.0 / 31
FROM PS_TRANSACTION_INV A, (PS_PURCH_ITEM_ATTR B LEFT OUTER JOIN
PS_ITEM_MFG C ON B.SETID = C.SETID AND B.INV_ITEM_ID = C.INV_ITEM_ID ),
PS_CM_ACCTG_DIST_D D
WHERE ( A.BUSINESS_UNIT = '11MMS'
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND C.PREFERRED_MFG = 'Y'
AND A.TRANSACTION_DATE >= '05-01-2018'
AND A.TRANSACTION_DATE <= '05-31-2018'
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.INV_ITEM_ID = D.INV_ITEM_ID
AND D.TRANSACTION_GROUP = A.TRANSACTION_GROUP
AND A.INV_ITEM_ID = '1')
) AS 'Daily Average'
FROM PS_TRANSACTION_INV A, (PS_PURCH_ITEM_ATTR B LEFT OUTER JOIN
PS_ITEM_MFG C ON B.SETID = C.SETID AND B.INV_ITEM_ID = C.INV_ITEM_ID ),
PS_CM_ACCTG_DIST_D D
WHERE ( A.BUSINESS_UNIT = '11MMS'
AND A.INV_ITEM_ID = B.INV_ITEM_ID
AND C.PREFERRED_MFG = 'Y'
AND A.TRANSACTION_DATE >= '05-01-2018'
AND A.TRANSACTION_DATE <= '05-31-2018'
AND A.BUSINESS_UNIT = D.BUSINESS_UNIT
AND A.INV_ITEM_ID = D.INV_ITEM_ID
AND D.TRANSACTION_GROUP = A.TRANSACTION_GROUP
AND A.INV_ITEM_ID = '1')
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句