I need to list the best-selling product per day in the store, showing the description, the type and the day.
As far as I've come:
SELECT Data, MAX(QtdVenda) as Vezes_Que_Foi_Vendido, IDProduto, Fabricante, tipo, nome
from vendas INNER JOIN produtos on QtdVenda=
(Select Max(QtdVenda) from vendas order by data)
where produtos.ID = vendas.IDProduto group by data order by data;
Output:
+------------+-----------------------+-----------+------------+------------+------+
| Data | Vezes_Que_Foi_Vendido | IDProduto | Fabricante | tipo | nome |
+------------+-----------------------+-----------+------------+------------+------+
| 2017-07-08 | 10 | 15 | SAMSUNG | Smartphone | S9 |
+------------+-----------------------+-----------+------------+------------+------+
Structure of my tables:
mysql> select * from produtos;
+------+------------------------+------------+------------+------------+------------+
| ID | Nome | Fabricante | Quantidade | VlUnitario | Tipo |
+------+------------------------+------------+------------+------------+------------+
| 1 | Playstation 3 | Sony | 100 | 2000 | Console |
| 2 | Core 2 Duo 4GB RAM 500 | DELL | 200 | 1900 | Notebook |
| 3 | XBOX 360 120 GB | Microsoft | 350 | 1300 | Console |
| 4 | GT-1620 QUAD BAND | SAMSUNG | 300 | 500 | Celular |
| 5 | iPHONE 4 32 MB | Apple | 50 | 1500 | Smartphone |
| 6 | Playstation 2 | Sony | 100 | 400 | Console |
| 7 | Wii 120 MB | Nintendo | 250 | 1000 | Console |
| 8 | S7 | SAMSUNG | 100 | 1500 | Smartphone |
| 9 | J7 | SAMSUNG | 300 | 1000 | Smartphone |
| 10 | iPHONE 5 32 MB | Apple | 150 | 2000 | Smartphone |
| 11 | Core 2 Duo 8 GB | DELL | 100 | 2500 | Notebook |
| 12 | Playstation 4 | Sony | 250 | 2500 | Console |
| 13 | Notebook | DELL | 200 | 3000 | Notebook |
| 14 | Ultrabook | DELL | 100 | 5500 | Ultrabook |
| 15 | S9 | SAMSUNG | 300 | 2500 | Smartphone |
+------+------------------------+------------+------------+------------+------------+
15 rows in set (0.05 sec)
mysql> select * from vendas;
+---------+------------+----------------+-----------+----------+----------+
| IDVenda | Data | Cliente | IDProduto | QtdVenda | Desconto |
+---------+------------+----------------+-----------+----------+----------+
| 1 | 2017-07-08 | Carlos Santana | 15 | 10 | 50 |
| 2 | 2017-07-09 | Ana Maria Melo | 7 | 1 | 100 |
| 3 | 2017-07-09 | Fernando Silva | 8 | 1 | 70 |
| 4 | 2017-07-09 | Fernando Souza | 13 | 2 | 150 |
| 5 | 2017-07-10 | Luis Gama | 3 | 1 | 50 |
| 6 | 2017-07-10 | Arthur Filho | 7 | 2 | 100 |
| 7 | 2017-07-10 | Fernando Silva | 12 | 1 | 50 |
| 8 | 2017-07-10 | Fernando Souza | 15 | 3 | 200 |
| 9 | 2017-07-12 | Ana Maria Melo | 1 | 3 | 200 |
| 10 | 2017-07-13 | Cassio Moreira | 5 | 1 | 50 |
+---------+------------+----------------+-----------+----------+----------+
10 rows in set (0.02 sec)