Grouping Causes Multiple Rows - Can This Be Avoided?

Question

Grouping Causes Multiple Rows - Can This Be Avoided?

AMCiti

SSC-Addicted

Points: 496
More actions
June 10, 2020 at 8:32 pm
Go to Answer
#3762114

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?

ChrisM@Work

SSC Guru

Points: 186127
More actions
June 11, 2020 at 11:15 am
Answer
#3762250
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

drew.allen SSC Guru Points: 76985 More actions · Answer 1

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

AMCiti SSC-Addicted Points: 496 More actions · Answer 2

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?

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 3

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

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 4

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.