How to select different values in different dates in SQL

Im trying to get the number of customers that a company had each day for the last 7 days. But when I run my code I get the total of customers for the week and is just displaying the last order date.

SELECT order_date, COUNT(DISTINCT buyerid) as 'customers' from orders
WHERE
date(order_date) >= curdate() - 7
AND date(order_date) <= curdate()

Answer

Your code is able to run because it’s not in ‘full group by only’ mode. That is because you’re not aggregating the order_date, so you end up displaying only the last date and counting all the buyerids. This is almost never an expected result, I might say.

I would rewrite it like so:

SELECT order_date, COUNT(DISTINCT buyerid) as 'customers'
FROM orders
WHERE
    order_date >= date_sub(curdate(), interval 1 week)
GROUP BY order_date

Now it will count the distinct buyerids in each day.

Also, curdate() - 7 doesn’t seem to work so I rewrote it entirely, assuming order_date is of date type.

Mind you might miss results if its a datetime, and in that case compare it to now() or strip time entirely.

I don’t get the meaning of the line AND date(order_date) = curdate(). It seems to be right only if you want today’s sales.