RETURN THE SUM OF MAXIMUM 3 SQL VALUES

Question

I have a table and I need to return the following: check based on recipient column, add the 3 largest amount of that recipient, bring in query only if the sum of the 3 largest ones are >= 1024:

CREATE TABLE TRANSFERS(
SENDER VARCHAR2(200) NOT NULL,
RECIPIENT VARCHAR2(200) NOT NULL,
DATA DATE NOT NULL,
AMOUNT INTEGER NOT NULL);


INSERT INTO TRANSFERS VALUES ('Smith', 'Williams', '01/01/2000', 200);
INSERT INTO TRANSFERS VALUES ('Smith', 'Taylor', '27/09/2002', 1024);
INSERT INTO TRANSFERS VALUES ('Smith', 'JOHNSON', '26/06/2005', 512);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '17/12/2010', 100);
INSERT INTO TRANSFERS VALUES ('Williams', 'JOHNSON', '22/03/2004', 10);
INSERT INTO TRANSFERS VALUES ('Brown', 'JOHNSON', '20/03/2013', 500);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '02/06/2007', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 400);
INSERT INTO TRANSFERS VALUES ('JOHNSON', 'Williams', '26/06/2005', 200);

in this scenario the SELECT should bring the names of Taylor and JHONSON because, Taylor in a transfer he already has the value of 1024, already Jhonson the sum of his 3 largest as Recipient is: 512, 500 e 100 = 1112;

I was only able to bring Taylor:

SELECT T.RECIPIENT AS NOME
FROM TRANSFERS T
WHERE T.AMOUNT >= 1024;

thank you!

Answer 1

Another approach, using row_number():

select 
    x.recipient,
    sum(x.amount) as soma
from 
    (select
         t.recipient,
         t.amount,
         row_number() over (partition by recipient order by amount desc) as i
     from transfers t) x
where x.i <=3
group by x.recipient
having sum(x.amount) >= 1024;

As for the performance, I don’t know which would be better, just by performing a test.

Answer 2

You can create a function PL/PgSQL to perform this calculation, see only:

CREATE OR REPLACE FUNCTION fc_obter_top3()
RETURNS TABLE ( RECIPIENT  VARCHAR(200), AMOUNT BIGINT ) AS 
$$
DECLARE
   rec RECORD;
BEGIN
   FOR rec IN SELECT trsf.RECIPIENT FROM TRANSFERS AS trsf GROUP BY trsf.RECIPIENT
   LOOP
       RETURN QUERY SELECT
                      rec.RECIPIENT,
                      SUM(top.AMOUNT) 
                    FROM
                      (SELECT t.AMOUNT FROM TRANSFERS AS t WHERE t.RECIPIENT = rec.RECIPIENT ORDER BY t.AMOUNT DESC LIMIT 3) AS top
                    HAVING
                      SUM(top.AMOUNT) >= 1024;
   END LOOP;
END;
$$
LANGUAGE plpgsql; 

Testing:

SELECT * FROM fc_obter_top3(); 

Exit:

| recipient | amount |
|-----------|--------|
|   JOHNSON |   1112 |
|    Taylor |   1024 |

Sqlfiddle

Answer 3

SELECT 
  T.RECIPIENT AS NOME,
  SUM(T_TMP.AMOUNT) AS TOTAL
FROM TRANSFERS T
JOIN (SELECT TOP 3 T2.RECIPIENT, T2.AMOUNT FROM TRANSFERS T2 ORDER BY T2.AMOUNT DESC) AS T_TMP  ON T.RECIPIENT = T_TMP.RECIPIENT
GROUP BY T.RECIPIENT
HAVING SUM(T.AMOUNT) >= 1024;

It’s not very performative but I believe it should solve the problem.

Obs: at that link it is possible to test the above solution (sql server).

---

edited

for postgresql (also with a example here):

SELECT 
  T.RECIPIENT AS NOME,
  SUM(T_TMP.AMOUNT) AS TOTAL
FROM TRANSFERS T
JOIN (SELECT T2.RECIPIENT, T2.AMOUNT FROM TRANSFERS T2 ORDER BY T2.AMOUNT DESC LIMIT 3) AS T_TMP  ON T.RECIPIENT = T_TMP.RECIPIENT
GROUP BY T.RECIPIENT
HAVING SUM(T.AMOUNT) >= 1024;