Return the highest SUM value of all donors by designations

Question

I have the following script:

SELECT DISTINCT GIFT_ID, GIFT_DESG, SUM(GIFT_AMT)
FROM GIFT_TABLE
GROUP BY GIFT_ID, GIFT_DESG

It will return something like this:

GIFT_ID         GIFT_DESG      SUM(GIFT_AMT)
      1                A               25
      1                B              500
      1                C               75
      2                A              100
      2                B              200
      2                C              300
      ...

My desired outcome is:

GIFT_ID         GIFT_DESG      SUM(GIFT_AMT)
      1                B           500
      2                C           300

How would I do that?

Possibly row_number() right? I think it's something with the summing of gift amounts by designation that is throwing me off.

Thank you.

Answer 1

if your DBMS support ROW_NUMBER window function you can try to make row number by GIFT_ID order by SUM(GIFT_AMT) then get rn = 1 row.

SELECT t1.GIFT_ID,t1.GIFT_DESG,t1.GIFT_AMT 
FROM (
    SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY GIFT_ID ORDER BY GIFT_AMT DESC) rn
    FROM (
      SELECT  GIFT_ID, GIFT_DESG, SUM(GIFT_AMT) GIFT_AMT
      FROM GIFT_TABLE
      GROUP BY GIFT_ID, GIFT_DESG
    ) t1
) t1
where rn =1

Note

You already use GROUP BY the DISTINCT keyword is no sense, you can remove it from your query.

---

Here is a sample

CREATE TABLE T(
   GIFT_ID int,
   GIFT_DESG varchar(5),
  GIFT_AMT int
);


insert into t values (1,'A' ,25);
insert into t values (1,'B' ,500);
insert into t values (1,'C' ,75);
insert into t values (2,'A' ,100);
insert into t values (2,'B' ,200);
insert into t values (2,'C' ,300);

Query 1:

SELECT t1.GIFT_ID,t1.GIFT_DESG,t1.GIFT_AMT 
FROM (
    SELECT t1.*,ROW_NUMBER() OVER(PARTITION BY GIFT_ID ORDER BY GIFT_AMT DESC) rn
    FROM T t1
) t1
where rn =1

Results:

| GIFT_ID | GIFT_DESG | GIFT_AMT |
|---------|-----------|----------|
|       1 |         B |      500 |
|       2 |         C |      300 |