Post reply

Grouping And Total

  • June 21, 2016 at 5:48 am

    #308548

    Hi Friends,

    I am trying to get the Total based on Sender column.

    I have attached the sample spread sheet.

    The First 3 rows are kind of duplicates but the RequestID and CustomerRef Column will be different.

    The 4th row is different.

    I want to sum the values based on "Sender". Sender is same but RequestFile will be different. If the Sender and RequestFile are same but occurred multiple times due to different RequestID column...Can it be considered as single row and sum the value?

    First three rows should be considered as single count. Please refer the spread sheet.

    If My question is not clear, Please do let me know.

    Thanks,
    Charmer

  • June 21, 2016 at 6:28 am

    #1885997

    It's an easy task. You must first eliminate the duplicate rows and then make the SUM-ing.

    According to your sample data, it should be somthing like this

    with SampleTable as

    (

    select

    [Format],[Response], [Sequence], [Response], [File],[File Status],[File Errors],

    [Cards],[Blocks],[Card Groups],[Successful Cards],[Successful Blocks],[Successful Card Groups],[Failed Cards],[Failed Blocks],

    [Failed Card Groups],[Request ID],[Customer Ref],[Request Type],[Request Status],

    ROW_NUMBER() OVER (PARTITION BY [Request ID],[Customer Ref] ORDER BY [Request ID],[Customer Ref]) AS RN

    )

    SELECT [Format],[Response], [Sequence], [Response], [File],[File Status],[File Errors],

    SUM([Cards]),SUM([Blocks]),SUM([Card Groups]),SUM([Successful Cards]),SUM([Successful Blocks]),SUM([Successful Card Groups]),

    SUM([Failed Cards]),SUM([Failed Blocks]),SUM([Failed Card Groups]),[Request ID],[Customer Ref],[Request Type],[Request Status]

    FROM SampleTable

    WHERE RN=1

    GROUP BY

    [Format],[Response], [Sequence], [Response], [File],[File Status],[File Errors],[Request ID],[Customer Ref],[Request Type],[Request Status]

    Igor Micev,My blog: www.igormicev.com

  • June 21, 2016 at 6:35 am

    #1885999

    Hi Igor,

    I want to do this in RDL. I can't use this SQL query. And SP is used in the Dataset.

    Thanks,
    Charmer

  • June 21, 2016 at 6:49 am

    #1886008

    Charmer (6/21/2016)

    Hi Igor,

    I want to do this in RDL. I can't use this SQL query. And SP is used in the Dataset.

    You can write similar form of it and put it in your SP for the Dataset.

    Igor Micev,My blog: www.igormicev.com

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply