CODEWITHSUNDEEP
sql
Eric R.
Eric R.

Reputation: 1125

Maximum Sum in SQL

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?

Upvotes: 0

Views: 342

Answers (3)

Sascha
Sascha

Reputation: 10347

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

Upvotes: 0

Jon Raynor
Jon Raynor

Reputation: 3892

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

Group By LastName

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

Upvotes: 1

Pheonix
Pheonix

Reputation: 6052

Change

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

to

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

Upvotes: 0

Related Questions