Grouping Causes Multiple Rows - Can This Be Avoided?

  • Dear Group:

    I have a query:

    SELECT

    SUM(Field1), SUM(Field2), SUM(Field3), SiteLocation, CalcField1

    FROM table1

    WHERE WorkDate BETWEEN @startDate AND @endDate

    GROUP BY

    SiteLocation, CalcField1

    The problem I am having is that the data should only return one record for each site.  The reason it isn't is because CalcField1 can have different values, so to group on this, in the result set gives me multiple records for each site.

    But in my SSRS report, I need this CalcField1 as I need to perform some calculations using it, but because it isn't a SUM or other function, I need to group on it for the query to work.  Is there a way around this or is this the best I can hope for, to have multiple records for each site?

  • You're either going to need to do your calculations in T-SQL and not group on CalcField1 or do your totals in SSRS.  If you want more details you're going to need to provide sample data and expected results.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • My apologies, but unfortunately, I am going to need more details.  Below is my stored procedure (at least the select portion causing this issue)

    SELECT              
         [Site],             
         ISNULL([LCRate], 0) AS LCRate,             
         SUM([Eligible]) AS [Eligible],             
         SUM(Offers) AS AllOffers,             
         SUM(Points) AS [Points]
    FROM             
         Agent      
    WHERE             
         WorkDay BETWEEN @startDate AND @endDate             
         AND (@extract = '_ALL' OR [Extract] IN (SELECT VALUE FROM dbo.[Fn_Parse_ParameterList]( @extract,':')))      
    GROUP BY             
        [Site],             
        [LCRate]

    But because of this, the result set looks like the following:  But I would only like to have one value for BAG-CV in my result set.

    I would like to change the stored procedure, but not sure how to go about doing this.

    In my SSRS reports then, I am using LCRate as follows in the column:

    =Code.Divide(Fields!Point.Value, Fields!Eligible.Value * Fields!LCRate.Value)

    I would prefer to somehow change my Stored Procedure include this calculation and have it as a new column (not needing to send LCRate to SSRS) so that I only have one record for Site, but not sure how to go about doing this.

    Hopefully this explanation makes more sense?

  • Which one of the rows for BAG-CV do you want?  The calculation you have here is going to produce a different result for each row - so how can you determine what that value should be if you return a single row?

     

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I think in this case if I understand things right, windowing functions are going to be your friend.

    something like:

    SELECT [site],
    ISNULL([LCRate],0) as LCRate,
    SUM([Eligible]) OVER (PARTITION BY [site]) AS [Eligible],
    SUM([Offers]) OVER (PARTITION BY [site]) AS [AllOffers],
    SUM([Points]) OVER (PARTITION BY [site]) AS [Points]
    FROM Agent
    WHERE [WorkDay] BETWEEN @startDate AND @endDate
    AND (@extract = '_ALL' OR [Extract] IN (SELECT VALUE FROM dbo.[Fn_Parse_ParameterList]( @extract,':')))

    That will still give you 3 rows for BAG-CV, but they will all have the same value for Eligible, Offers, and Points.

    If that is no good, I think you are going to need 2 data sets so you can pull LCRate and Site in one data set, and then pull Site, Eligible, Offers and Points in the other.

    The above is all just my opinion on what you should do. 
    As with all advice you find on a random internet forum - you shouldn't blindly follow it.  Always test on a test server to see if there is negative side effects before making changes to live!
    I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

  • AMCiti wrote:

    My apologies, but unfortunately, I am going to need more details.  Below is my stored procedure (at least the select portion causing this issue)

    SELECT              
         [Site],             
         ISNULL([LCRate], 0) AS LCRate,             
         SUM([Eligible]) AS [Eligible],             
         SUM(Offers) AS AllOffers,             
         SUM(Points) AS [Points]
    FROM             
         Agent      
    WHERE             
         WorkDay BETWEEN @startDate AND @endDate             
         AND (@extract = '_ALL' OR [Extract] IN (SELECT VALUE FROM dbo.[Fn_Parse_ParameterList]( @extract,':')))      
    GROUP BY             
        [Site],             
        [LCRate]

    But because of this, the result set looks like the following:  But I would only like to have one value for BAG-CV in my result set.

    I would like to change the stored procedure, but not sure how to go about doing this.

    In my SSRS reports then, I am using LCRate as follows in the column:

    =Code.Divide(Fields!Point.Value, Fields!Eligible.Value * Fields!LCRate.Value)

    I would prefer to somehow change my Stored Procedure include this calculation and have it as a new column (not needing to send LCRate to SSRS) so that I only have one record for Site, but not sure how to go about doing this.

    Hopefully this explanation makes more sense?

    You have shown what your result set looks like, can you show what you would like it to look like? Thanks.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 6 posts - 1 through 5 (of 5 total)

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