SUM([value]) and highest record type

Question

Post reply

SUM([value]) and highest record type

Pete Bishop

Default port

Points: 1491

June 27, 2025 at 8:05 pm

#4614358

Given a record mapping table:

Id    Initial    Updated
1     1          2
2     3          4
3     6          5

and a data table:

Id    Model    RecordType    Value
1     1        1             3.00
2     1        2             5.00
3     2        3             2.00
4     3        6             1.00
5     3        5             2.00

I want to SUM([Value]), grouped by the [Model] but also to return the [RecordType] of the highest transaction. So if it has been updated, return 2, 4 or 5 otherwise return. 1, 3 or 6.

I have the code:

SELECT
    [data].[Model],
    SUM([data].[Value]) AS [Value]
FROM
    [data]
INNER JOIN
    [mapping]
        ON [data].[RecordType] IN ([mapping].[Initial], [mapping].[Updated])
GROUP BY
    [data].[Model],
    [mapping].[Id]

I've "solved it" using the following code but I'm not sure it's the best method - it certainly doesn't work if historical data is uploaded in a different order:

SELECT
    [Model],
    SUM([Value]) AS [Value],
    (SELECT [RecordType] FROM @data WHERE [Id] = [maxId]) AS [RecordType]
FROM (
    SELECT
        [data].[Model],
        SUM([data].[Value]) AS [Value],
        MAX([data].[Id]) AS [maxId]
    FROM
        [data]
    INNER JOIN
        [mapping]
            ON [data].[RecordType] IN ([mapping].[Initial], [mapping].[Updated])
    GROUP BY
        [data].[Model],
        [mapping].[Id]
) AS [sq]
GROUP BY
    [Model],
    [maxId]

Thanks.

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80356 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Steve Collins SSCrazy Points: 2227 More actions · Answer 2

DROP TABLE IF EXISTS #data_table;
GO
CREATE TABLE #data_table (
    Id INT PRIMARY KEY,
    Model INT,
    RecordType INT,
    Value DECIMAL(10,2));

INSERT INTO #data_table (Id, Model, RecordType, Value) VALUES
(1, 1, 1, 3.00),
(2, 1, 2, 5.00),
(3, 2, 3, 2.00),
(4, 3, 6, 1.00),
(5, 3, 5, 2.00);

DROP TABLE IF EXISTS #mapping;
GO
CREATE TABLE #mapping (
    Id INT PRIMARY KEY,
    Initial INT,
    Updated INT);

INSERT INTO #mapping (Id, Initial, Updated) VALUES
(1, 1, 2),
(2, 3, 4),
(3, 6, 5);

with sum_cte as (
    select *, sum([value]) over (partition by Model) sum_val,
           row_number() over (partition by Model order by [Value] desc) rn
    from #data_table)
select *, isnull(oa.Updated, sc.RecordType) updated_record_type
from sum_cte sc
     outer apply (select Updated
                  from #mapping m
                  where m.Initial=sc.RecordType) oa(Updated)
where sc.rn=1;

This query totals the [Values] and finds the highest [Value] for each Model. For the top [Value], it checks if there's an updated record type in the mapping table — if so, it selects the updated one; if not, it keeps the original

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können