# Selecting MIN and MAX value from Grouped Values

### 질문

• Coulumn A      ColumnB    COlumnC                ColumnD

XYZ                    10                20                 10 Jul 2018

XYZ                    15                11                 12 Jul 2018

ABC                   12                35                  16 Jul 2018

ASD                   34                20                  17 Jul 2018

BBB                   55                 18                  17 Jul 2018

BBB                   10                 34                  17 Jul 2018

BBB                   25                 28                  18 Jul 2018

With this data when I use COUNT(DISTINCT ColumnD) ABC, how can I get Min(VALUE) AND Max(Value) After doing SUM of Same Date Values. For Ex: Column B - 55+10, 25 and selecting MIN and MAX from this.

Thanks!

2018년 7월 13일 금요일 오후 2:11

### 답변

• Thanks, I have achieved this.

Name Amount  VAT   Days Min Max
ABC    12.00    35.00    1    12    12
ASD    34.00    20.00    1    34    34
BBB    90.00    80.00    2    25    65
XYZ    25.00    31.00    2    10    1

Scenario is in Name -BBB

```CREATE TABLE test(
[Name] VARCHAR(3) NOT NULL
,Amount INTEGER  NOT NULL
,Tax INTEGER  NOT NULL
,[Date] Date NOT NULL
);
INSERT INTO test([Name],Amount,Tax,[Date]) VALUES
('XYZ',10,20, 'Jul 10 2018')
,('XYZ',15,11, 'Jul 12 2018')
,('ABC',12,35, 'Jul 16 2018')
,('ASD',34,20, 'Jul 17 2018')
,('BBB',55,18, 'Jul 17 2018')
,('BBB',10,34, 'Jul 17 2018')
,('BBB',25,28, 'Jul 18 2018');
SELECT [Name],SUM(Amount) AS AMount,SUM(VAT) AS VAT,COUNT([Date]) AS Days,MIN(Amount) AS MinAmt,MAX(AMount) AS MaxAmtFROM(
SELECT [Name],SUM(Amount) AS AMount,SUM(VAT) AS VAT,[Date]
FROM test
GROUP BY [Name],[Date])tGROUP BY [Name]```

if you see, this is exactly similar to what I suggested earlier

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

2018년 7월 13일 금요일 오후 3:29
• Hi Vism,

Did you mean this?

```CREATE TABLE #test(
ColumnA VARCHAR(3) NOT NULL
,ColumnB INTEGER  NOT NULL
,COlumnC INTEGER  NOT NULL
,ColumnD Date NOT NULL
);
INSERT INTO #test(ColumnA,ColumnB,COlumnC,ColumnD) VALUES
('XYZ',10,20, 'Jul 10 2018')
,('XYZ',15,11, 'Jul 12 2018')
,('ABC',12,35, 'Jul 16 2018')
,('ASD',34,20, 'Jul 17 2018')
,('BBB',55,18, 'Jul 17 2018')
,('BBB',10,34, 'Jul 17 2018')
,('BBB',25,28, 'Jul 18 2018');

select t1.ColumnA, t1.CountNum, MAX(t2.SumNum) as MaxNum, Min(t2.SumNum) as MinNum
from (
select ColumnA,COUNT(distinct ColumnD) as CountNum
from #test
group by ColumnA ) t1
join (
select ColumnA,ColumnD,SUM(ColumnB) as SumNum
from #test
group by ColumnA,ColumnD ) t2 on t1.ColumnA = t2.ColumnA
group by t1.ColumnA, t1.CountNum
```

Thanks,
Xi Jin.

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

2018년 7월 16일 월요일 오전 3:17

### 모든 응답

• Please learn how to post your question with scripts for table DDL, insert into and your expected result. Thanks.
2018년 7월 13일 금요일 오후 2:15
• sounds like this

```SELECT ColumnA,
COUNT(ColumnD) AS DistinctDays,
MIN(BSum) AS MinBSum,
MAX(BSum) AS MaxBSum
FROM
(
SELECT ColumnA,ColumnD,SUM(ColumnB) AS BSum
FROM TableName
GROUP BY ColumnA,ColumnD
)t
GROUP BY ColumnA```

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

2018년 7월 13일 금요일 오후 2:20
• Your question should starts like this and let us know your expected result.

```CREATE TABLE test(
CoulumnA VARCHAR(3) NOT NULL
,ColumnB INTEGER  NOT NULL
,COlumnC INTEGER  NOT NULL
,ColumnD Date NOT NULL
);
INSERT INTO test(CoulumnA,ColumnB,COlumnC,ColumnD) VALUES
('XYZ',10,20, 'Jul 10 2018')
,('XYZ',15,11, 'Jul 12 2018')
,('ABC',12,35, 'Jul 16 2018')
,('ASD',34,20, 'Jul 17 2018')
,('BBB',55,18, 'Jul 17 2018')
,('BBB',10,34, 'Jul 17 2018')
,('BBB',25,28, 'Jul 18 2018');

Select * from test

drop table test```

2018년 7월 13일 금요일 오후 2:22
• Let us know your expected result in a tabular format to easy understand your question.

2018년 7월 13일 금요일 오후 2:53
• Based on your description your query looks good. Please post your expected output (not words but the result set that you want for the given input data). That way should be able to help you. Thanks.

HTH,
Cheers!!
Ashish

2018년 7월 13일 금요일 오후 2:58
• Thanks, I have achieved this.
2018년 7월 13일 금요일 오후 3:24
• You may post your final query here to conclude your post. Thanks.
2018년 7월 13일 금요일 오후 3:27
• Thanks, I have achieved this.

Name Amount  VAT   Days Min Max
ABC    12.00    35.00    1    12    12
ASD    34.00    20.00    1    34    34
BBB    90.00    80.00    2    25    65
XYZ    25.00    31.00    2    10    1

Scenario is in Name -BBB

```CREATE TABLE test(
[Name] VARCHAR(3) NOT NULL
,Amount INTEGER  NOT NULL
,Tax INTEGER  NOT NULL
,[Date] Date NOT NULL
);
INSERT INTO test([Name],Amount,Tax,[Date]) VALUES
('XYZ',10,20, 'Jul 10 2018')
,('XYZ',15,11, 'Jul 12 2018')
,('ABC',12,35, 'Jul 16 2018')
,('ASD',34,20, 'Jul 17 2018')
,('BBB',55,18, 'Jul 17 2018')
,('BBB',10,34, 'Jul 17 2018')
,('BBB',25,28, 'Jul 18 2018');
SELECT [Name],SUM(Amount) AS AMount,SUM(VAT) AS VAT,COUNT([Date]) AS Days,MIN(Amount) AS MinAmt,MAX(AMount) AS MaxAmtFROM(
SELECT [Name],SUM(Amount) AS AMount,SUM(VAT) AS VAT,[Date]
FROM test
GROUP BY [Name],[Date])tGROUP BY [Name]```

if you see, this is exactly similar to what I suggested earlier

Visakh
----------------------------
My Wiki User Page
My MSDN Page
My Personal Blog

2018년 7월 13일 금요일 오후 3:29
• Hi Vism,

Did you mean this?

```CREATE TABLE #test(
ColumnA VARCHAR(3) NOT NULL
,ColumnB INTEGER  NOT NULL
,COlumnC INTEGER  NOT NULL
,ColumnD Date NOT NULL
);
INSERT INTO #test(ColumnA,ColumnB,COlumnC,ColumnD) VALUES
('XYZ',10,20, 'Jul 10 2018')
,('XYZ',15,11, 'Jul 12 2018')
,('ABC',12,35, 'Jul 16 2018')
,('ASD',34,20, 'Jul 17 2018')
,('BBB',55,18, 'Jul 17 2018')
,('BBB',10,34, 'Jul 17 2018')
,('BBB',25,28, 'Jul 18 2018');

select t1.ColumnA, t1.CountNum, MAX(t2.SumNum) as MaxNum, Min(t2.SumNum) as MinNum
from (
select ColumnA,COUNT(distinct ColumnD) as CountNum
from #test
group by ColumnA ) t1
join (
select ColumnA,ColumnD,SUM(ColumnB) as SumNum
from #test
group by ColumnA,ColumnD ) t2 on t1.ColumnA = t2.ColumnA
group by t1.ColumnA, t1.CountNum
```