Get the summation a specific column and a last value of a specific column in each group in a result of SQL query using SQL Server

Question

I'm working on a query that return a result set divided in groups, I want to get the summation of column "MA" with the last value of column "DATE" in each group, All of my attempts are resulting in wrong summation and wrong last value.

This one of my attempts: here when I do ORDER BY "DATE" in the ROW_NUMBER() it get different results, I can't do ORDER BY another column because its useless unless I used ROW_NUMBER() in ORDER BY and this is impossible.

WITH cte AS
(
    SELECT 
        SUM([ADB_LAST].[MA]) AS [MA],
        SUM([ADB_LAST].[DA]) AS [DA], 
        [ADB_LAST].[ID_BAS], 
        [ADB_LAST].[PRO_NUMBER], 
        [ADB_LAST].[ACC_NUMBER],
        [ADB_LAST].[DATA], [ADB_LAST].[DATE] AS MyDate,
        [ADB_LAST].[Q], 
        [ADB_LAST].[P], 
        RNum = ROW_NUMBER() OVER (PARTITION BY [ADB_LAST].[ACC_NUMBER] ORDER BY [ADB_LAST].[DATE] DESC),
        [ADB_LAST].[UNIT], 
        [ADB_LAST].[ID], 
        SUM([ADB_LAST].[R]) AS [R]
    FROM 
        [ADB_LAST](@PRO_NAME, @SDAY, @FDAY)
    GROUP BY 
        [ACC_NUMBER], [PRO_NUMBER], [DATA], [Q], [P],
        [UNIT], [ID], [ID_BAS], [DATE]
    HAVING 
        SUM([R]) <> 0
)
SELECT 
    MA, DA, 
    [ID_BAS],
    [PRO_NUMBER],
    [ACC_NUMBER],
    [DATA],
    [Date],
    [Q], [P],
    [UNIT],
    [ID],
    R
FROM 
    cte
WHERE 
    RNum = 1

I've asked a similar question before and the answer solved my problem at that time but this time with the expected results it's not going as I wish.

Here is a screenshot of the result in which column "MA" has correct summation values but column "DATE" has wrong values, the date of first row must be 27/01/2008 which is the DATE value of the last row in the result of the previous query.

You cannot group by Date and calculate the correct sum. That's the first problem. You said the sum is correct - I don't think it is. The second problem is you need to define exactly what "last" means. It looks like your attempt computed the max value of date (which is far simpler than using row_number descending and grabbing the first row). I doubt that is what you wanted - the partitioning is likely very incomplete. And stop using the arcane from clause syntax — SMor
You need provide some details. Things like the table structure (create table statements) and sample data (insert statements) along with the desired output for the sample data. We will want to see the ddl for that table valued function also. We can't see your screen or read your mind. — Sean Lange
I can't provide sample data because I'm querying from result of inline function that query view and so on... — xamo
Not sure how that prevents you from sharing sample data. As I said we would want to see the table valued function. Nobody can help you without a lot of guessing unless you provide details. The result of this question is up to you. If you provide the details needed I will be happy to help you. But if you don't then you are on your own. — Sean Lange

Sean Lange Sean Lange · Accepted Answer · 2019-10-16T20:28:51

This isn't really an answer but it is too long for a comment, and the comments don't support decent formatting.

Speaking of formatting....adding some to your query along with an alias turns that wall of indecipherable text into something easy to dissect and understand.

WITH cte AS
(
    SELECT 
        SUM(l.MA) AS MA
        , SUM(l.DA) AS DA
        , l.ID_BAS
        , l.PRO_NUMBER
        , l.ACC_NUMBER
        , l.DATA
        , l.[DATE] as MyDate --you don't want to use reserved words as column names
        , l.Q
        , l.P
        , RNum = ROW_NUMBER() OVER (PARTITION BY l.ACC_NUMBER ORDER BY l.[DATE] desc)
        , l.UNIT
        , l.ID
        , SUM(l.R) AS R
    FROM ADB_LAST(@PRO_NAME, @SDAY, @FDAY) l
    GROUP BY ACC_NUMBER
        , PRO_NUMBER
        , DATA
        , Q
        , P
        , UNIT
        , ID
        , ID_BAS
        , DATE
    HAVING Sum(R) <> 0
)
SELECT MA
    , DA
    , ID_BAS
    , PRO_NUMBER
    , ACC_NUMBER
    , DATA
    , MyDate
    , Q
    , P
    , UNIT
    , ID
    , R
FROM cte
WHERE RNum = 1

Now if we only understood what the problem is you are facing.

Get the summation a specific column and a last value of a specific column in each group in a result of SQL query using SQL Server

3 Answers