SQL Max and Sum
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
Linco开发者_JS百科ln 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
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
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
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
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
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.
精彩评论