SQL Max and Sum

Question

Below is my query that I am using:

SELECT 
   County,
   Code,
   Sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County,
   Code

It returns the set:

County     Code         TotalPaid
Brown      99           210.21
Lyon       73           322.22
Lyon       88           533.22
Lincoln    22           223.21

What I am looking for is a query that will return the rows that show the County and the Code for the Max TotalPaid for each County. An example of the result set that I need is shown below (notice that Lyon, 73 is removed since Lyon, 88 has a higher TotalPaid amount):

County     Code         TotalPaid
Brown      99           210.21
Lyon       88           533.22
Lincoln    22           223.21

What database is this? Oracle, SQL Server, other?

Mark Sherretta
– Mark Sherretta

2011-08-18 19:46:14 +00:00
Commented Aug 18, 2011 at 19:46 — Mark Sherretta
– Mark Sherretta, Commented Aug 18, 2011 at 19:46

Mark Sherretta · Accepted Answer · 2011-08-18 19:55:06Z

2

I wasn't able to test this, but RANK should solve this:

SELECT x.County, x.Code x.TotalPaid
    ,RANK() OVER 
    (PARTITION BY x.County ORDER BY x.TotalPaid DESC) AS 'RANK'
FROM

(SELECT 
   County,
   Code,
   Sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County,
   Code) x
WHERE Rank = 1

answered Aug 18, 2011 at 19:55

Mark Sherretta

10.3k4 gold badges40 silver badges43 bronze badges

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

Comments

Dave Costa · Accepted Answer · 2011-08-18 19:57:59Z

1

I think you need to do something like the follwoing. I've just been called away before I could review what I've written but hopefully it will give you enough of a pointer. Some RDBMSes won't allow the "where country, TotalPaid = select value, value" construct but you can work around this

select 
   County,
   Code,
   TotalPaid
from (SELECT 
       County,
       Code,
       Sum(PaidAmount) AS TotalPaid
     FROM
       Counties
     GROUP BY
       County,
       Code ) tbl
 where County, TotalPaid = (select County, 
                                 max(TotalPaid)
                             FROM
                                 Counties
                             GROUP BY
                                 County,
                                 Code ) tbl2

edited Aug 18, 2011 at 19:57

Dave Costa

48.3k8 gold badges61 silver badges73 bronze badges

answered Aug 18, 2011 at 19:51

Karl

3,38223 silver badges27 bronze badges

1 Comment

Dave Costa Over a year ago

Don't know about SQL Server, but in Oracle the syntax would be where (county,totalpaid) IN (select ...)

Hugo Cantacuzene · Accepted Answer · 2011-08-18 19:51:28Z

0

   SELECT 
   c.County,
   c.Code,
   Sum(c.PaidAmount) AS TotalPaid
   FROM
   Counties c
   WHERE
   c.Code in (select max(c2.code) from counties c2 where c2.county = c.county)
   GROUP BY
   c.County,
   c.Code

this one should work although i haven't tested

answered Aug 18, 2011 at 19:51

Hugo Cantacuzene

851 silver badge7 bronze badges

1 Comment

Dave Costa Over a year ago

He doesn't want the maximum code value, he wants the code that has the maximum TotalPaid within each county.

Adam Robinson · Accepted Answer · 2011-08-18 19:53:10Z

0

You'll have to use windowing functions to do this. While what you want is easily expressed in english, it's not easily expressed in SQL, unfortunately. This should do what you need:

select
    County, Code, TotalPaid
from
(
SELECT 
   County,
   Code,
   sum(PaidAmount) AS TotalPaid
FROM
   Counties
GROUP BY
   County, Code
) source

where (row_number() over (partition by County order by TotalPaid desc)) = 1

answered Aug 18, 2011 at 19:53

Adam Robinson

186k36 gold badges294 silver badges351 bronze badges

Comments

Briguy37 · Accepted Answer · 2011-08-18 20:11:18Z

-2

Here's an updated solution:

select c1.county, c1.code, c1.paidAmount 
from counties c1
inner join (
  select county, max(paidAmount) paidAmount 
  from counties 
  group by county) c2 
on c1.county=c2.county and c1.paidAmount=c2.paidAmount;

Note, if there are multiple max payments for a certain county, this will return all rows that share that maximum.

edited Aug 18, 2011 at 20:11

answered Aug 18, 2011 at 19:46

Briguy37

8,4523 gold badges36 silver badges53 bronze badges

3 Comments

Eric R. Over a year ago

That will give me the sum for the Max Code value for each county, which isn't what I want. I want to Display the County and Code for the rows with the Max TotalPaid.

Briguy37 Over a year ago

@Eric R: Sorry, realized that just after I posted. Looking into the best way of doing that...

Briguy37 Over a year ago

@Eric R: Updated my solution.

Collectives™ on Stack Overflow

SQL Max and Sum

5 Answers 5

Comments

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

1 Comment

1 Comment

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related