Sum和Group by Query [英] Sum and Group by Query

问题描述

我想在下面的脚本中总结和分组，无论我做什么都不会。

SUM用于iv.untqty，我想通过pm.prtfam分组，我还需要日期来引入日期而不是时间。

任何帮助将不胜感激

SELECT 

pm.prtfam， 

iv.rcvdte已收到， 

SUM（iv.untqty）为TOTAL_QTY，

t.arrdte到达， 

t.CLOSE_DTE已结束

FROM 

inventory_view iv， 

prtmst pm， 

ARC_rcvlin rl， 

ARC_rcvtrk rt， 

ARC_rcvinv ri， 

ARC_trlr t 

GROUP BY pm.prtfam

WHERE  ; $
iv.prtnum = pm.prtnum 

AND iv.prt_client_id = pm.prt_client_id 

AND iv.rcvkey = rl.rcvkey  ; $
AND rl.invnum = ri.invnum 

AND rl.wh_id = ri.wh_id 

AND ri.trknum = rt。 trknum&NBSP ; $
AND rt.trlr_id = t.trlr_id 

AND rt.trkref NOT LIKE'00840％' 

AND rl.wh_id ='4300'

AND pm.wh_id_tmpl ='4300'
$
AND（iv.rcvdte to to_date（'11 / 01/2017 00:00:00'，' MM / DD / YYYY HH24：MI：SS'）

和TO_DATE（'12 / 01/2017 00:00:00'，'MM / DD / YYYY HH24：MI：SS'））



订购费用 

iv.rcvdte

Oracle数据库11g企业版11.2.0.4.0版 - 64位生产

PL / SQL版本11.2.0.4.0 - 生产

核心 11.2.0.4.0
生产

TNS for Linux：版本11.2 .0.4.0 - 生产

NLSRTL版本11.2.0.4.0 - 生产

兼容版本11.2.0.4.0

NLS_CHARACTERSET：AL32UTF8

NLS_LENGTH_SEMANTICS：BYTE

Java代码页：windows-1252

TIA Lisa

解决方案

这是一个SQL论坛，而不是Oracle。但是：

SELECT 

pm.prtfam，  

iv.rcvdte Recieved， 

SUM（ iv.untqty）为TOTAL_QTY，

t.arrdte到达， 

t.CLOSE_DTE已关闭

FROM 

inventory_view iv， 

prtmst pm， 

ARC_rcvlin rl， 

ARC_rcvtrk rt， 

ARC_rcvinv ri， 

ARC_trlr t 

FROM 

inventory_view iv， 

prtmst pm， 

ARC_rcvlin rl， 

ARC_rcvtrk rt， 

ARC_rcvinv ri， 

ARC_trlr t 

WHERE 

iv.prtnum = pm.prtnum 

AND iv.prt_client_id = pm.prt_client_id 

AND iv.rcvkey = rl.rcvkey 

AND rl.invnum = ri.invnum 

AND rl.wh_id = ri.wh_id 

AND ri.trknum = rt.trknum 

AND rt.trlr_id = t.trlr_id 

AND rt.trkref NOT LIKE'00840％' 

AND rl.wh_id ='4300'

AND pm.wh_id_tmpl ='4300'

AND（iv.rcvdte to to_date（'11 / 01/2017 00:00:00'，'MM / DD / YYYY HH24：MI：SS'）

AND TO_DATE（'12 / 01/2017 00:00:00'，'MM / DD / YYYY HH24：MI：SS'））

分组依据

pm.prtfam， 

iv.rcvdte， 

t.arrdte， 

t.CLOSE_DTE

如果这篇文章对你有帮助，请标记为答案

I am trying to sum and group by in the following script and no matter what I do it will not go.

SUM is for iv.untqty and I want to group by the pm.prtfam, I also need the dates to just bring in the date and not the time.

Any help would be appreciated

SELECT 
pm.prtfam, 
iv.rcvdte Recieved, 
SUM(iv.untqty) as TOTAL_QTY,
t.arrdte Arrived, 
t.CLOSE_DTE Closed
FROM 
inventory_view iv, 
prtmst pm, 
ARC_rcvlin rl, 
ARC_rcvtrk rt, 
ARC_rcvinv ri, 
ARC_trlr t 

GROUP BY pm.prtfam

WHERE 
iv.prtnum = pm.prtnum 
AND iv.prt_client_id = pm.prt_client_id 
AND iv.rcvkey = rl.rcvkey 
AND rl.invnum = ri.invnum 
AND rl.wh_id = ri.wh_id 
AND ri.trknum = rt.trknum 
AND rt.trlr_id = t.trlr_id 
AND rt.trkref NOT LIKE '00840%' 
AND rl.wh_id = '4300'
AND pm.wh_id_tmpl = '4300'
AND (iv.rcvdte between to_date ('11/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE ('12/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))

ORDER BY 
iv.rcvdte

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
Compatible Version 11.2.0.4.0
NLS_CHARACTERSET: AL32UTF8
NLS_LENGTH_SEMANTICS: BYTE
Java code page: windows-1252

TIA Lisa

解决方案

This is a SQL Forum, not Oracle. However:

SELECT 
pm.prtfam, 
iv.rcvdte Recieved, 
SUM(iv.untqty) as TOTAL_QTY,
t.arrdte Arrived, 
t.CLOSE_DTE Closed

FROM 
inventory_view iv, 
prtmst pm, 
ARC_rcvlin rl, 
ARC_rcvtrk rt, 
ARC_rcvinv ri, 
ARC_trlr t 

FROM 
inventory_view iv, 
prtmst pm, 
ARC_rcvlin rl, 
ARC_rcvtrk rt, 
ARC_rcvinv ri, 
ARC_trlr t 

WHERE 
iv.prtnum = pm.prtnum 
AND iv.prt_client_id = pm.prt_client_id 
AND iv.rcvkey = rl.rcvkey 
AND rl.invnum = ri.invnum 
AND rl.wh_id = ri.wh_id 
AND ri.trknum = rt.trknum 
AND rt.trlr_id = t.trlr_id 
AND rt.trkref NOT LIKE '00840%' 
AND rl.wh_id = '4300'
AND pm.wh_id_tmpl = '4300'
AND (iv.rcvdte between to_date ('11/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
AND TO_DATE ('12/01/2017 00:00:00', 'MM/DD/YYYY HH24:MI:SS'))

Group by 

pm.prtfam, 
iv.rcvdte, 
t.arrdte, 
t.CLOSE_DTE

Please mark as answer if this post helped you

这篇关于Sum和Group by Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！