MySQL Make multiple accounts with a different WHERE clause and see the result

advertisements

I am trying to get from my orders table: the number of orders each customer has placed within a week, then do another count for the next week, then again, and again, etc.

I am using COUNT but it keeps bringing back an empty result.

Here is my query.

SELECT `uid`, (SELECT COUNT(`uid`) FROM `orders` WHERE `order_timestamp` > '1476707688' and `order_timestamp` < '1476189288') as 'number1',
       (SELECT COUNT(`uid`) FROM `orders` WHERE `order_timestamp` > '1476189288' and `order_timestamp` < '1475584488') as 'number2',
       (SELECT COUNT(`uid`) FROM `orders` WHERE `order_timestamp` > '1475584488' and `order_timestamp` < '1474979688') as 'number3'
FROM `orders` ORDER BY `uid` ASC

The result looks like this:

I know that there are multiple orders in a week from multiple customers.

How do you do that query? A query that will bring back the number of orders a customer has made in that time period?

Cheers,


Just use conditional aggregation:

SELECT `uid`,
       SUM(`order_timestamp` > '1476707688' and `order_timestamp` < '1476189288') as number1,
       SUM(`order_timestamp` > '1476189288' and `order_timestamp` < '1475584488') as number2,
       SUM(`order_timestamp` > '1475584488' and `order_timestamp` < '1474979688') as number3
FROM `orders`
GROUP BY `uid` ASC;

Note: this aggregates by uid, so there will be one row per uid rather than per order. That seems like the sensible thing to do.