SQL Query calculation across tables

Question

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]

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.

Which version of SQL Server? Here are some examples of percent of base calculations in SQL: http://sqlusa.com/bestpractices/percentonbase/ https://stackoverflow.com/questions/770579/how-to-calculate-percentage-with-a-sql-statement. Have a go although I'm sure someone will soon post a solution for you. — Nick.Mc, Sep 06 '17 at 03:03
The formula says `SUM(X_QTY)`. Is it the SUM of the whole table? — Vladimir Baranov, Sep 06 '17 at 03:54

Vladimir Baranov · Accepted Answer · 2017-09-06T04:22:19.230

2

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.

edited Sep 06 '17 at 04:22

answered Sep 06 '17 at 04:04

Vladimir Baranov

31,799
5
53
90

Error: An arithmetic overflow error occurred while converting expression to data type int. – Cát Tường Vy Sep 06 '17 at 04:10
@user3035133, 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. I updated the answer. – Vladimir Baranov Sep 06 '17 at 04:22
How to cast this one to Decimal : SUM(a.[X_QTY]) OVER (PARTITION BY a.X_PO_NO) AS RM_QTYPerUnit – Cát Tường Vy Sep 06 '17 at 05:37
@user3035133, you need to decide what scale and precision are appropriate in your case. The syntax is the same as shown in the answer: `SUM(CAST(a.[X_QTY] AS decimal(38,2) )) OVER (...)` – Vladimir Baranov Sep 06 '17 at 05:43
OK! I got it . But my result is 1094.339622 I just need 1094.34. Thank a lot Vladimir Baranov – Cát Tường Vy Sep 06 '17 at 06:06
@user3035133, normally you should round the result on the client as you display it to the end user. If you really want to do it on the server, there is [`ROUND`](https://learn.microsoft.com/en-us/sql/t-sql/functions/round-transact-sql) function. – Vladimir Baranov Sep 06 '17 at 06:15

SQL Query calculation across tables

1 Answers1