Select mysql of a ranking

Question

Well I’m setting up a ranking of donations, the table in the database is like this:

    --
-- Estrutura da tabela `Doacoes`
--

CREATE TABLE `Doacoes` (
`Cod` int(11) NOT NULL,
`Doador` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
`Data` datetime NOT NULL,
`Valor` decimal(7,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Extraindo dados da tabela `Doacoes`
--

INSERT INTO `Doacoes` (`Cod`, `Doador`, `Data`, `Valor`) VALUES
(100, 'Hugo Borges', '2019-11-28 00:00:00', '10.00'),
(101, 'Hugo Borges', '2019-11-28 00:00:00', '10.00'),
(102, 'Rafael', '2019-11-28 00:00:00', '30.00');

Well I’m putting together a select that will give me the top 10 donors. But note that I have to join the same name, add the total donation and display the date of the last donation.

Can someone give me a hand with this?

Answer 1

Hugo,

To add up the values, you use the SUM of SQL.

To bring the last date, you can use the MAX of SQL.

To limit to ten records, you can use the LIMIT mysql.

And finally, to join the names, in the query, you do the GROUP BY SQL by the field Donor.

---

See an example of this query:

SELECT SUM(Valor) Valor
     , Doador
     , MAX(Data) data
  FROM Doacoes
 GROUP BY Doador
 LIMIT 10;

Look at the fiddle: http://sqlfiddle.com/#! 9/b849030/3

Answer 2

SELECT Doador,
       SUM(Valor) as TotalDoado,
       MAX(Data) as UltimaDoacao
  FROM Doacoes
 GROUP BY Doador
 LIMIT 10;