none
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

모든 응답

  • 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


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    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
    Please mark it as Answered if it answered your question or mark it as Helpful if it helped you solve your problem.

    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 MaxAmt
    FROM
    ( SELECT [Name],SUM(Amount) AS AMount,SUM(VAT) AS VAT,[Date] FROM test GROUP BY [Name],[Date]

    )t
    GROUP BY [Name]


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

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    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
    

    If it doesn't satisfy your requirement, please share us more detailed information about your logic and your expected result based on your sample data.

    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