What I've done:
USE [cpqaAnswers]
GO
CREATE TABLE [cpqaAnswers].[cpqa].[tbl_SK_typSubmit_01](
[Id] INT,
[SubmissionDate] DATE,
[SubmissionType] NVARCHAR(256),
[Amount] DECIMAL(10,2),
[PaidAmount] DECIMAL(10,2),
[Unique_Id] NVARCHAR(27)
)
USE [cpqaAnswers]
GO
BULK INSERT [cpqaAnswers].[cpqa].[tbl_sk_typSubmit_01]
FROM 'C:\Users\ks\data.txt'
data.txt content:
Check ...
USE [cpqaAnswers]
GO
SELECT [Id]
,[SubmissionDate]
,[Amount]
,[PaidAmount]
,[Unique_Id]
FROM [cpqa].[tbl_sk_typSubmit_01]
GO
Id SubmissionDate Amount PaidAmount Unique_Id
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1 2020-01-04 1166.00 562.00 test123
2 2020-06-01 -1166.00 -562.00 test123
3 2020-06-01 1166.00 579.00 test123
4 2020-11-02 587.00 0.00 test123
Poster's query:
USE [cpqaAnswers]
GO
Select Count(Id) As [Count], Sum(PaidAmount) As [PaidAmount], Max(Case When Amount <=0 then 0 else Id end) As [LastId]
from [cpqa].[tbl_sk_typSubmit_01]
Group by unique_Id
What I get as a return:
Count PaidAmount LastId
~~~~~~~~~~~~~~~~~~~~~~~~~~
4 579.00 4
And although you state that the desired output under [LastId] is "3", it never will be "3", it will always be the last row of data [LastId] because the conditional "<=0 THEN 0 ELSE Id END" (as you can see in the data) IS NEVER "0"; the last row "4".
So you've got youself a Type II error in you reasoning.