SQL - Searching for best-selling product in a date range

Navigation
1

I'm trying to fetch the best-selling SQL product within a certain time period. For this, I have the table 'product_address', which has the following columns:

id (PK) | account_id (FK) | product_id (FK) | precoFinal | created_at | updated_at

Well, I obviously have the produto table and the contas table.

Before I had done a SQL that can get the best selling product using a 'count', descending order and limiting the result to the best seller, as follows: 

SELECT nome, urlImagem
FROM produtos
WHERE id = (
    SELECT produto_id
    FROM conta_produtos
    GROUP BY produto_id
    ORDER BY count(*) DESC
    LIMIT 1
);

But now I want to limit this SQL to a certain period, ie, to find out which was the best-selling product in a given date range.

So I think I have to change the SELECT from within. I changed it, tried to elaborate a reasoning, but unfortunately the SQL has some error and I can not see what it is. My SQL: 

SELECT produto_id
FROM conta_produtos
GROUP BY produto_id
ORDER BY count(*) DESC
WHERE updated_at BETWEEN '2017-03-03' AND '2017-03-08'

What I realized was that if I put the last line (the main line of my sql, because it does the date filter), SQL does not work. Will this group by id get the most recent date between two equal IDs? Is this what's upsetting my where?

Thank you!

    
asked by anonymous 07.03.2017 / 15:42

2 answers

5

The WHERE clause must precede GROUP BY and ORDER BY. Example: 

SELECT produto_id
FROM conta_produtos
WHERE updated_at BETWEEN '2017-03-03' AND '2017-03-08' 
GROUP BY produto_id
ORDER BY count(*) DESC
    
07.03.2017 / 15:47
0

Apart from the fact that group by and order by should come at the end after the where, it may be interesting to include the count column with a nickname. 

SELECT 
    produto_id, count(produto_id) as contagem_produto
FROM 
    conta_produtos
WHERE 
    updated_at BETWEEN '2017-03-03' AND '2017-03-08' 
GROUP BY 
    produto_id
ORDER BY 
    contagem_produto DESC
    
07.03.2017 / 16:01
Find equal values between 2 different .csv columns and update another column X Fade effect run in jQuery