SQL merge and sum multiple rows into one row-CodePudding

I have joined multiple tables and obtained a resultset that has different rows for the same id or say, type, but they have different column values.

Eg.

| Number     | creation_date | value_1 | value_2 | value_3(value_1-value_2)
| --------   | --------      | ------  | ------  | ----- |
| 33445500   | 2022-03-20    | 30000   | 0       | 30000 |
| 33445500   | 2022-03-25    | 30000   | 0       | 30000 |
| 33445511   | 2022-03-25    | 20000   | 10000   | 10000 |
| 33445512   | 2022-01-01    | 20000   | 10000   | 10000 |
| 33445512   | 2022-03-25    | 20000   | 0       | 20000 |

I would like the result as follows:

| Number     | creation_date | value_1 | value_2 | value_3(value_1-value_2)
| --------   | --------      | ------  | ------  | ----- |
| 33445500   | 2022-03-25    | 60000   | 0       | 60000 |
| 33445511   | 2022-03-25    | 20000   | 10000   | 10000 |
| 33445512   | 2022-03-25    | 40000   | 10000   | 30000 |

How can this be done in SQL?
I would appreciate any kind of help. Thank you!


select 
CONVERT( date, SYSDATETIME() ) as some_date,
LEFT(value_1,9) as value_1,

from database
join  a__ on a__.a__b = b__.bbb_ref
join  k__ on k__.kae__ref = b__.bbb__ref
join  af__ on af__.af_ref = b__.af__ref

where lorem ipsum

CodePudding user response：

Try this

SELECT 
    Number, 
    MAX(creation_date) creation_date, 
    SUM(value1) value1,
    SUM(value2) value2,
    SUM(value3) value3
FROM myTable
GROUP BY Number

CodePudding user response：

Kind of like the other answer, but somewhat different:

SELECT
        Number
    ,   MAX(creation_date) AS [creation_date]
    ,   SUM(value_1) AS [value_1]
    ,   SUM(value_1) AS [value_1]
    ,   SUM(value_1 - value_2) AS [value_3]
FROM result_set
GROUP BY Number
ORDER BY Number, MAX(creation_date)