Is there a way to insert the most frequently occurring value in SQL?-CodePudding

I need to INSERT data from one table into another after doing a quick calculation in MySQL as to compare the user's transaction amounts (500, 150 and 100) by their total sums (500 * 2 = 1000, 150 * 3 = 450, 100 * 5 = 500) and INSERT the transaction amount with the highest sum (500) into another table (donationrequests) as the ASK.

How can I compare multiple records and then INSERT only one in MySQL? I'm struggling with this part. Below is my schema and where I am at currently.

CREATE TABLE transactions
(
  DONOR_ID TINYTEXT NOT NULL,
  TRANSACTION_AMT INT
);

INSERT INTO transactions
(
  DONOR_ID, TRANSACTION_AMT
) VALUES 
("ALLEN", 500 ),
("ALLEN", 500 ),
("ALLEN", 150 ),
("ALLEN", 150 ),
("ALLEN", 150 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 ),
("ALLEN", 100 )

CREATE TABLE donationrequests
(
  DONOR_ID TINYTEXT NOT NULL,
  ASK int
  PRIMARY KEY (USER)
);

INSERT INTO donationrequests (DONOR_ID, ASK)
SELECT DISTINCT DONOR_ID AS d, SUM(TRANSACTION_AMT) AS t
FROM transactions
GROUP BY d
ORDER BY t DESC;

This produces ("ALLEN", 1950) in donationrequests It should produce ("ALLEN", 500)

CodePudding user response：

You can use ROW_NUMBER to rank on the SUM of transactions per donos, then insert in "donationrequests" only rows whose ranking is 1.

INSERT INTO donationrequests 
WITH cte AS(
    SELECT *, 
           ROW_NUMBER() OVER(PARTITION BY DONOR_ID 
                             ORDER     BY SUM(TRANSACTION_AMT) DESC
           ) AS rn
    FROM transactions
    GROUP BY DONOR_ID, TRANSACTION_AMT
)
SELECT DONOR_ID, TRANSACTION_AMT 
FROM cte 
WHERE rn = 1

Check the demo here.