CODEWITHSUNDEEP
sql-servert-sql
Saaif
Saaif

Reputation: 41

Find out max and min value for a column based on other column condition in SQL Server

I have a following table

CREATE TABLE #Table1
(
    [RowID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
    , [Column1] BIGINT NOT NULL
    , [Column2] CHAR(10) NOT NULL
    , [Column3] CHAR(5) NOT NULL
    , [Amount]  NUMERIC(16, 4) NOT NULL DEFAULT 0.0000
    , [SequenceNumber] INT NOT NULL
    , [SequenceType] CHAR(1) NOT NULL
)


INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50019.0000, 1, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50021.0000, 2, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50023.0000, 3, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50025.0000, 4, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50027.0000, 5, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50029.0000, 6, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50033.0000, 8, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50035.0000, 9, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 2754246.0000, 10, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 2660870.0000, 11, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 7938347.0000, 12, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 7951196.0000, 13, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00964', 2660870.0000, 11, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00964', 7938347.0000, 12, 'D');

SELECT * FROM #Table1;

DROP TABLE #Table1

Table rows

enter image description here

I need to find out max and min Amount based on column "SequenceType" value for column "Column1", "Column2" and "Column3".

Output columns: Column1, Column2, Column3, MaxAmountForSeqTypeP, MinAmountForSeqTypeP, MaxAmountForSeqTypeD, MinAmountForSeqTypeP

Expected Output

enter image description here

Upvotes: 1

Views: 1422

Answers (1)

MJH
MJH

Reputation: 1750

Something like this:

select Column1, Column2, Column3, 
    max(case when SequenceType = 'P' then amount else null end) MaxP, 
    min(case when SequenceType = 'P' then amount else null end) MinP,
    max(case when SequenceType = 'D' then amount else null end) MaxD, 
    min(case when SequenceType = 'D' then amount else null end) MinD
from Table1
group by Column1, Column2, Column3

Or this might be faster:

;with cte as (
     select Column1, Column2, Column3, 
         case when SequenceType = 'P' then amount else null end P,
         case when SequenceType = 'D' then amount else null end D
     from Table1
)
select Column1, Column2, Column3, Max(P) MaxP, Min(P) MinP, Max(D) MaxD, MIn(D) MinD
from cte
group by Column1, Column2, Column3

Upvotes: 4

Related Questions