Performing a sum of a calculated column in SQL Server - Stack Overflow

admin管理员组

文章数量:1122832

I need to write a SQL query that adds a new calculated column from existing columns in the table.

The existing columns are: ProductInv, IntCantInv and SalCantInv.

The calculated column is called DIF.

I need to add the entire calculated column DIF. Is there any way to do it?

Here is my SQL query that works well:

SELECT 
    ProductInv, IntCantInv, SalCantInv, 
    (IntCantInv - SalCantInv) AS DIF 
FROM 
    TABLA_MOVIMIENTO_INVENTARIOS 
WHERE
    ProductInv = 10001001008

Thank you very much for your help.

I need to write a SQL query that adds a new calculated column from existing columns in the table.

The existing columns are: ProductInv, IntCantInv and SalCantInv.

The calculated column is called DIF.

I need to add the entire calculated column DIF. Is there any way to do it?

Here is my SQL query that works well:

SELECT 
    ProductInv, IntCantInv, SalCantInv, 
    (IntCantInv - SalCantInv) AS DIF 
FROM 
    TABLA_MOVIMIENTO_INVENTARIOS 
WHERE
    ProductInv = 10001001008

Thank you very much for your help.

• sql
• sql-server

Share Improve this question Follow edited Nov 22, 2024 at 19:14 marc_s 754k183183 gold badges1.4k1.4k silver badges1.5k1.5k bronze badges asked Nov 22, 2024 at 17:12 Bladimir Silva ToroBladimir Silva Toro 744 bronze badges 3

• 1 It's not clear what you're asking for. – Joel Coehoorn Commented Nov 22, 2024 at 17:16
• 1 What's wrong with what you have? If any of the original values change, then your calculated field will be invalid – Reimeus Commented Nov 22, 2024 at 17:17
• Do you mean you want to add another row with a total sum? SQL Server is not excel, so you shouldn't do this normally, but check out GROUPING SETS perhaps – siggemannen Commented Nov 22, 2024 at 17:26

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 1

Here's a couple of variations which might be useful to you:

SELECT  *
INTO #data
FROM    (
    VALUES  (10001001008, 636, 1251)
    ,   (10001001008, 920, 928)
    ,   (10001001008, 992, 1033)
    ,   (10001001008, 1051, 1241)
    ,   (10001001008, 1218, 1143)
    ,   (10001001008, 1271, 1137)
    ,   (10001001008, 1273, 1470)
    ,   (10001001008, 1291, 1331)
    ,   (10001001008, 1345, 1780)
    ,   (10001001008, 1414, 864)
    ,   (10001001008, 2087, 1261)
) t (Productoinv,EntCantinv,SalCantInv)

SELECT  Productoinv, ISNULL(EntCantinv, 0) AS EntCantinv, ISNULL(SalCantInv, 0) AS SalCantInv, SUM(EntCantinv - SalCantInv) AS Dif
FROM    #data
GROUP BY GROUPING SETS ((Productoinv, EntCantinv,SalCantInv),(Productoinv))

SELECT  Productoinv, EntCantinv, SalCantInv, EntCantinv - SalCantInv Dif, SUM(EntCantinv - SalCantInv) OVER(PARTITION BY Productoinv) AS TotalDif
FROM    #data

First one adds a new summary row, while second adds a new column containing summarized value.

I think what you're trying to do is sum the differences, but it's not clear. If that interpretation is correct, you can do this:

SELECT SUM(EntCantInv-SalCantInv) AS TotalDIF 
FROM TABLA_MOVIMIENTO_INVENTARIOS 
Where ProductInv=10001001008

For the sample data, that would give you this result:

TotalDIF
59

本文标签： Performing a sum of a calculated column in SQL ServerStack Overflow