CODEWITHSUNDEEP
sqlsql-serversql-server-2008percentage
Cát Tường Vy
Cát Tường Vy

Reputation: 398

SQL Query calculation across tables

I have 3 tables that are joined using the following query:

SELECT 
    b.[_PRESS_INST_NO]
    ,a.[X_PO_NO]
    ,a.[X_GOODS_CD]
    ,a.[X_QTY]
    ,c.RM_CD
    ,c.RM_QTY
FROM 
    T1 a
    INNER JOIN T2 b ON a.X_PO_NO=b.X_PO_NO
    INNER JOIN T3 c ON b._PRESS_INST_NO=c.[_PRESS_INST_NO]

enter image description here

My desired result is RM_QTYPerUnit that can be done by Excel formula:

RM_QTYPerUnit = X_QTY * RM_QTY / SUM(X_QTY)

Total RM_QTY of X_PO_NO: VG00181 is 2320, and RM_QTY for X_GOODS_CD : 332034960 is 1094.34 not 2320.

Upvotes: 1

Views: 197

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32685

If I understood you correctly, you need SUM partitioned by X_PO_NO. It is easy to get using the OVER clause:

SELECT 
    b.[_PRESS_INST_NO]
    ,a.[X_PO_NO]
    ,a.[X_GOODS_CD]
    ,a.[X_QTY]
    ,c.RM_CD
    ,c.RM_QTY
    ,a.[X_QTY] * c.RM_QTY / 
    SUM(CAST(a.[X_QTY] AS float)) OVER (PARTITION BY a.X_PO_NO) AS RM_QTYPerUnit
FROM 
    T1 a
    INNER JOIN T2 b ON a.X_PO_NO=b.X_PO_NO
    INNER JOIN T3 c ON b._PRESS_INST_NO=c.[_PRESS_INST_NO]
;

If the X_QTY values are so large that their sum doesn't fit into 4-byte int, then cast them to float or decimal with appropriate scale and precision.

Upvotes: 2

Related Questions