SQL : get number of user but order date [closed]

I trying to get how many user i got in a precise day compared to the first order for exemple :

"2020-02" = 20, "2020-03" = 20 ... etc...

I did an sql query like that :

SELECT o.order_id, u.id as userId, DATE_FORMAT(FROM_UNIXTIME(o.order_created),'%Y-%m'), COUNT(u.id) as userCount
FROM users u
LEFT JOIN order_product o ON o.order_user_id = u.id
GROUP BY u.id;

but with this query i have something like this exemple:

"2020-02" = 2, "2020-02" = 9, "2020-03" = 5, "2020-03" = 7

so i tried to group by u.id and DATE_FORMAT(FROM_UNIXTIME(o.order_created),'%Y-%m') like this exemple :

SELECT o.order_id, u.id as userId, DATE_FORMAT(FROM_UNIXTIME(o.order_created),'%Y-%m'), COUNT(u.id) as userCount
FROM users u
LEFT JOIN order_product o ON o.order_user_id = u.id
GROUP BY u.id, DATE_FORMAT(FROM_UNIXTIME(o.order_created),'%Y-%m');

but it still does not work and in the 2 case, the result of the ‘userCount’ is not good.

So my question is, how can i get a number of users in grouped in specific month?

Answer

I think you are asking for a pretty simple aggregation:

SELECT DATE_FORMAT(FROM_UNIXTIME(o.order_created), '%Y-%m') as yyyymm, 
       COUNT(*) as num_orders,
       COUNT(DISTINCT o.order_user_id) as num_users
FROM users u
GROUP BY yyyymm;

I’m a bit baffled why you are joining order_products when that table is not being used. And the join conditions also don’t make sense.

Advertisement

Answer