none
Group By + more than one column RRS feed

  • Question

  • Hi all,

    I'm triyng to retrieve info in this way:

    I have the next output:

    Id     Currency     Rate

    1       1                5 

    1       1                4

    1       2                4

    2       1                7               

    2       1                8

    The info i need is: I want the ID's that have the same currency and this currency have distict values for the same ID. In this case, the output will be:

    ID   Different Count

    1     2

    2     2

    It's possible to group by, counting for more than one column at same time?

    Thanks in advance.

    IGF

    Tuesday, July 25, 2017 10:35 AM

All replies

  • I have problems understanding what you mean. Can you clarify the description and also add more sample data?

    Tuesday, July 25, 2017 7:40 PM
    Moderator
  • Try following,

    DECLARE @Table TABLE
    (
      ID int,
      Currency Varchar(100),
      Rate Int
    )
    
    INSERT INTO @Table values
    (1,'1',5),
    (1,'1',4),
    (1,'2',4),
    (2,'1',7),
    (2,'1',8)
    
    SElect ID, IDD As 'Different Count' 
    From
    (
    Select	*, ROW_NUMBER() Over(Partition By ID, Currency Order By ID) IDD 
    From	@Table
    ) A 
    Where IDD > 1

    Thursday, July 27, 2017 4:08 AM