I am trying to sum up two rows. I have a table1 as the below:

I need to get the difference of the two rows as the below:

I tried using the below statement:

SELECT TOP(1)
   'DEFICIT' AS [MEASURES]
    APR - lag(APR, 1, 0) OVER (ORDER BY [MEASURES]) AS APR
   ,MAY - lag(MAY, 1, 0) OVER (ORDER BY [MEASURES]) AS MAY
   ,JUN - lag(JUN, 1, 0) OVER (ORDER BY [MEASURES]) AS JUN
   ,JUL - lag(JUL, 1, 0) OVER (ORDER BY [MEASURES]) AS JUL
   FROM table1
   ORDER BY [MEASURES] DESC;

But doesn't result correctly. I am not sure how to get the difference. Can you please point me to some solution. Thanks in advance.

CodePudding user response：

Just conditionally sum the columns

SELECT 'Total'
    , SUM(CASE WHEN MEASURES = 'Measure 2' THEN -1 ELSE 1 END * APR) APR
    , SUM(CASE WHEN MEASURES = 'Measure 2' THEN -1 ELSE 1 END * MAY) MAY
    , SUM(CASE WHEN MEASURES = 'Measure 2' THEN -1 ELSE 1 END * JUN) JUN
    , SUM(CASE WHEN MEASURES = 'Measure 2' THEN -1 ELSE 1 END * JUL) JUL
FROM table1;

CodePudding user response：

select 'Total' as Measure, sum(APR) as APR,sum(MAY) as MAY,sum(JUNE) as JUNE,sum(JULY) as JULY
from table1;

CodePudding user response：

EDITED.

To sum up all rows in SQL Server in each column and create a single resulting row, you can use the SUM function with the GROUP BY clause.

SELECT SUM(APR), 
       SUM(MAY), 
       SUM(JUN), 
       SUM(JUL)
FROM table1
GROUP BY 1

You can use this approach to sum up the values in any number of rows, as long as they have compatible data types in the columns that you want to sum up. The GROUP BY 1 clause is used to indicate that all rows should be grouped together into a single group, which allows the SUM function to return a single result for the entire table.

-------------- EDIT -------------

General answer would something like this

-- Compute the difference between rows 1 and 2
SELECT id,
       col1 - col1 AS col1,
       col2 - col2 AS col2,
       col3 - col3 AS col3,
       col4 - col4 AS col4
FROM example_table
WHERE id = 1
UNION
SELECT id,
       col1,
       col2,
       col3,
       col4
FROM example_table
WHERE id = 2;