Maximum Sum in SQL

Question

I have a query that find the last name of a person, sum of amounts paid, and the code for the person. I need to create a query that returns the last name and the code for the largest sum. For Example, if I start with this:

SELECT
         LastName,
    SUM(a.NetPaidAmount) AS TotalPaid,
    Code1,
...

And it returns this set:

LastName   TotalPaid    Code1
Brown      264.26       295.30
Brown      1014.43      295.60
Brown      2960.98      295.70
Johnson    14098.84     295.30

I want my new query to return the rows

LastName  Code1
Brown     295.70
Johnson   295.30

How can this be done?

To write the query out, we really need to know what you're grouping by. I suspect, however, that ranking functions are what you want (ROW_NUMBER() OVER(ORDER BY Code1 DESC)) — Code Magician
– Code Magician, Commented Aug 17, 2011 at 19:42
To answer the question you have actually posted (rather than the answer you seem to have accepted), it would be helpful to know the flavor of SQL you are using. — Clockwork-Muse
– Clockwork-Muse, Commented Aug 17, 2011 at 20:17

Jon Raynor · Accepted Answer · 2011-08-17 19:55:10Z

1

Select LastName, SUM(a.NetPaidAmount) AS TotalPaid, (MAX)Code1, ...

Group By LastName

Group by last name, apply MAX function to code 1.

edited Aug 17, 2011 at 19:55

answered Aug 17, 2011 at 19:41

Jon Raynor

3,9126 gold badges33 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jon Raynor Over a year ago

I agree, his question chagned orginally, I am redoing my answer.

Eric R. Over a year ago

I can't do Top 1 because I want the Max Paid for EACH last name.

Clockwork-Muse Over a year ago

Except the max code returned won't actually be related to the sum - it's just going to be the maximum Code1 value avaliable for that LastName. And I wasn't aware that (MAX)[fieldName] was valid SQL syntax...

mikeY Over a year ago

I think I agree with X-Zero. Despite this answer being accepted by the OP I don't see it working. In the data provided it is luck that the max code is paired with the max totalpaid. I say "I think" because with the information provided by the OP it is difficult to say anything with certainty.

Pheonix · Accepted Answer · 2011-08-17 19:42:35Z

0

Change

SELECT LastName, SUM(a.NetPaidAmount) AS TotalPaid, Code1, ...

to

SELECT LastName, SUM(a.NetPaidAmount) AS TotalPaid, Code1, ...... Order by TotalPaid DESC LIMIT 1

answered Aug 17, 2011 at 19:42

Pheonix

6,0627 gold badges32 silver badges48 bronze badges

Comments

Sascha · Accepted Answer · 2011-08-17 20:12:13Z

You should apply grouping. Something like

SELECT
    LastName,
    SUM(NetPaidAmount) AS TotalPaid
FROM
    XYZ
GROUP BY
   LastName

The exact grouping may differ according to the columns you want to output.

Update:

If you groupy additionally by Code1, then the sum actually sums all NetPaidAmount where Lastname together with Code1 is unique. If you want to have the maximum additionally to another grouping level you must combine two queries. One with your groping and one that groupy only over lastname.

Not tested, written out of my head:

SELECT 
    A.Lastname, 
    A.TotalPaid, 
    B.Code1
FROM ( SELECT
           LastName,
           SUM(NetPaidAmount) AS TotalPaid
       FROM
           XYZ
       GROUP BY
           LastName ) A
INNER JOIN ( SELECT 
                 Lastname, 
                 Code1
             FROM
                 XYZ
             GROUP BY
                 Lastname, Code1 ) B ON B.Lastname = A.Lastname

My SQL is T-SQL flavor as I'm used to query against Microsft SQL Server, and this is most likely not the fastest was to do such things. So based on what DB you are using, the SQL might look different.

Update 2:

( I had to reformat you desired output to understand more, you can use code sections to output tables as they respect spaces )

I do not see the need for NetPaidAmount for the desired output. The desired output should be produced with something like this ( based on the evidence you provided ):

SELECT
    Lastname,
    MAX ( Code1 ) As Code1
FROM
    XYZ
GROUP BY
    Lastname

More details are not visible from your question and you should provide more information to get more help on which direction

He needs the 'NetPaidAmount' because he needs the Code1 value for the greatest sum (not the greatest Code1 value). At least, that's how Im reading it...

Collectives™ on Stack Overflow

Maximum Sum in SQL

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related