开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜