Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Group By Query  How to select the largest value

Posted on 2009-02-20
14
Medium Priority
?
1,134 Views
Last Modified: 2012-05-06
I have a table with:
NAME    Dist-CC   Loc-CC   Rev

I use the attached code to consolidate the data by Name and Dist-CC.  The initial intent was to get one record per NAME because each person was only associated with a single Dist-CC.  Now I am getting data where a NAME is associated with 2 or 3 Dist-CC so I am getting 2 to 3 records with the same name.

Is there a way to Group on NAME and get the Dist-CC that is associated with the largest sum of REV.  As an example I currently get:

NAME          Dist-CC    Loc-CC     REV
John Doe    5000         1234         $500
John Doe    6000         1234         $2000

I would like to get a single record for
NAME          Dist-CC    Loc-CC     REV
John Doe    6000         1234         $2,500    

i.e. - Because the 6000 Dist-CC is associated with the $2,000 (largest REV value) I would like to assign all the revenue ($500 + $2500) to the 6000 Dist-CC.

I have tried sorting the input date in descending order and using FIRST[QT].[Dist-CC] but that did not get the results I was hoping for.  Any suggestions would be appreciated if this is possible.


SELECT [Setup#1].[Name], [QT].[Dist-CC] AS [Dist-CC], First([QT].[Loc-CC]) AS [Loc-CC], Sum([Setup#1].Revenue) AS Rev
FROM [Setup#1] LEFT JOIN [QT] ON [Setup#1].CostCenter = [QT].[Loc-CC]
GROUP BY [Setup#1].[Name], [QT].[Dist-CC];

Open in new window

0
Comment
Question by:Jerry Paladino
  • 7
  • 6
14 Comments
 
LVL 41

Expert Comment

by:Sharath
ID: 23698903
check this.
SELECT T1.Name, T1.[Dist-CC], T1.[Loc-CC],T2.SUM_Revenue
  FROM Setup#1 AS T1
  JOIN (SELECT Name,MAX(Revenue) AS MAX_Revenue,SUM(Revenue) AS SUM_Revenue FROM Setup#1 GROUP BY Name) AS T2
    ON T1.Name = T2.Name AND T1.Revenue = T2.MAX_Revenue

Open in new window

0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23702578
Sharath,

First - My apologies - I attempted to disguise some of the table names and field names in the first SQL statement I included thinking that I could easily translate an experts reply back to my table names and field names.  Well, I should not have done that.  I have included the exact SQL that is currently working in the code section below.  Will you please try again with the understanding that I can produce SQL via the drag and drop interface but cannot write SQL from scratch.  I can usually read a SQL statement that has been written but your code is beyond by capabilities.  I think the "T1" is a temporary result set that you are acting on in the second part of the statement but I am really not sure.

If you could please try again using my table names and field names I will take what you provide and paste it into the Access SQL screen and try again.  Again - my apologies and I do appreciate your help.

ProdOps
SELECT [SalesSetup#1].[Sales Rep], [Quota-Tier].[Dist-CC], Last([Quota-Tier].[Loc-CC]) AS [Loc-CC], Sum([SalesSetup#1].Revenue) AS Rev
FROM [SalesSetup#1] LEFT JOIN [Quota-Tier] ON [SalesSetup#1].CostCenter = [Quota-Tier].[Loc-CC]
GROUP BY [SalesSetup#1].[Sales Rep], [Quota-Tier].[Dist-CC];

Open in new window

0
 
LVL 41

Expert Comment

by:Sharath
ID: 23702701
Try this. If you still not getting correct result, then can you post sample data from both your tables and expected result.
SELECT T1.[Sales Rep],T3.[Dist-CC],T3.[Loc-CC],T2.Sum_Revenue
  FROM [SalesSetup#1] T1
  JOIN (SELECT [Sales Rep],MAX(Revenue) AS Max_Revenue, SUM(Revenue) AS Sum_Revenue FROM [SalesSetup#1] GROUP BY [Sales Rep]) T2
    ON T1.[Sales Rep] = T2.[Sales Rep] AND T1.Revenue = T2.Max_Revenue
  JOIN (SELECT [Loc-CC],MAX([Dist-CC]) AS [Dist-CC] FROM [Quota-Tier] GROUP BY [Loc-CC]) T3 ON T1.CostCenter = T3.[Loc-CC]

Open in new window

0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23702906
When I paste it into the Access SQL screen for a new query I get "Syntax Error in FROM Clause".
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23702979
Sharath,

The attached Excel file has two sheets.  One with sample data for the SalesSetup#1 table and another for the Quota-Tier table.  Use the Salesrep=Sally as the example within the data tables.
There are four records for Sally in the table SalesSetup#1
Item      Revenue      Sales Rep      CostCenter
Pears      $300      Sally      2500
Peanuts      $400      Sally      2500
Crackers      $500      Sally      2500
Cereal      $100      Sally      2300

The Quota-Tier table has:
Loc-CC      Dist-CC
2300      5000
2500      6000

When the two tables are joined with CostCenter---Loc-CC, the results are:
SalesRep      Dist-CC      Revenue
Sally      5000      $100
Sally      6000      $1200

The result set I am looking for is to GROUP BY SalesRep and have a single record for Sally that is
SalesRep      Dist-CC      Revenue
Sally      6000      $1300

The Dist-CC 6000 has the largest revenue value($1200) so I would like to consolidate all of Sallys revenue($1200+$100=$1300) into Dist-CC 6000

I hope this is helpful and thank you again for your time!
ProdOps

Data-Table-Samples.xls
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23703009
can you try this?
SELECT T1.[Sales Rep],T3.[Dist-CC],T3.[Loc-CC],T2.Sum_Revenue
  FROM (([SalesSetup#1] T1
  JOIN (SELECT [Sales Rep],MAX(Revenue) AS Max_Revenue, SUM(Revenue) AS Sum_Revenue FROM [SalesSetup#1] GROUP BY [Sales Rep]) T2
    ON T1.[Sales Rep] = T2.[Sales Rep] AND T1.Revenue = T2.Max_Revenue)
  JOIN (SELECT [Loc-CC],MAX([Dist-CC]) AS [Dist-CC] FROM [Quota-Tier] GROUP BY [Loc-CC]) T3 ON T1.CostCenter = T3.[Loc-CC])
 

Open in new window

0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23703035
I get syntax error in join operation.  I included a screen shot of the results.  Let me know if I am doing this incorrectly.
Thank You,
ProdOps
Syntax-Error.jpg
0
 
LVL 58

Expert Comment

by:harfang
ID: 23703072
Hello again Sharath! The bracketing looks right, but Access also wants to know whether to use LEFT JOIN, RIGHT JOIN, or INNER JOIN (no other choices). I'm guessing INNER JOIN for the first and LEFT JOIN for the second?

(°v°)
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23703506
Thanks Harfang. I hope that will work for ProdOps. Basically I am SQL Server guy.  So don't know much about Access syntax.
SELECT T1.[Sales Rep],T3.[Dist-CC],T3.[Loc-CC],T2.Sum_Revenue
  FROM (([SalesSetup#1] T1
 INNER JOIN (SELECT [Sales Rep],MAX(Revenue) AS Max_Revenue, SUM(Revenue) AS Sum_Revenue FROM [SalesSetup#1] GROUP BY [Sales Rep]) T2
    ON T1.[Sales Rep] = T2.[Sales Rep] AND T1.Revenue = T2.Max_Revenue)
  LEFT JOIN (SELECT [Loc-CC],MAX([Dist-CC]) AS [Dist-CC] FROM [Quota-Tier] GROUP BY [Loc-CC]) T3 ON T1.CostCenter = T3.[Loc-CC])

Open in new window

0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23703541
Sharath,

I really appreciate your help with this.  This version of the SQL came back with a Circular reference in the Select statement.  I included a screen shot with the error message.  I certainly understand if you feel you have spent too much of your time on this question.  You have been great so far but I know you are volunteering your personal time.

ProdOps
Circular-Reference.jpg
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 23703668
can you try this?
SELECT T3.[Sales Rep],T4.[Dist-CC],T4.[Loc-CC],T3.Sum_Revenue
  FROM (([SalesSetup#1] T1
 INNER JOIN (SELECT [Sales Rep],MAX(Revenue) AS Max_Revenue, SUM(Revenue) AS Sum_Revenue FROM [SalesSetup#1] GROUP BY [Sales Rep]) T2
    ON T1.[Sales Rep] = T2.[Sales Rep] AND T1.Revenue = T2.Max_Revenue) T3
  LEFT JOIN (SELECT [Loc-CC],MAX([Dist-CC]) AS [Dist-CC] FROM [Quota-Tier] GROUP BY [Loc-CC]) T4 ON T3.CostCenter = T4.[Loc-CC])

Open in new window

0
 
LVL 16

Author Closing Comment

by:Jerry Paladino
ID: 31549814
Sharath,

The last SQL statement had a syntax error in Access but I am accepting your solution because as I have studied your SQL statement you provided me the ideas I needed to solve this problem.  Instead of doing everything in a single SQL statement I was able to write several small queries that did the intermediate steps you had consolidated into a single statement.  One to find the total revenue by Sales Rep.  Another to find the max revenue by Dist-CC. Another to join them together, etc&., etc&   The results of one being the input for the next query.  Probably not the most efficient way to do this but processing time is not a concern.  Only about 40,000 records and it is only run once per month.

I want to thank you again for spending your personal time on this.  You got me to see some new methods and new ways of solving these problems.  You have been fantastic expert to work with.  I look forward to working though another problem with you in the future.

THANK YOU!
Jerry
0
 
LVL 41

Expert Comment

by:Sharath
ID: 23706011
Thanks Jerry for your comments. With minor changes we can convert the sql queries written in one db provider to execute in another db provider. like sql server to oracle or from oracle to mysql etc... Access has some different syntax when JOINing the tables and the syntax is different in some more areas. Access in one area in which I need to improve my skills. Its not  an impossible task but I didn't get chance to work with Access db. Next time I will try to give you more syntaxical error free solutions.
Have a nice day,
Sharath
 
0
 
LVL 16

Author Comment

by:Jerry Paladino
ID: 23706427
Your welcome Sarath.  I actually learned more on this question so I am glad it turned out the way it did.

Take Care,
Jerry
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Technology opened people to different means of presenting information, but PowerPoint remains to be above competition. Know why PPT still works today.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question