CODEWITHSUNDEEP
mysql
Juned Ansari
Juned Ansari

Reputation: 5283

yearly count total number of peoples who is frequently ordering atleast one time in a month

I am trying to calculate yearly total number of peoples who is actively ordering atleast one time in a month. but it returns 0.

Try:

SELECT COUNT(DISTINCT(o.CreatedBy)) FROM orders o 
WHERE 
(o.CreatedDate >= '2017-02-01' AND o.CreatedDate < '2017-03-01') AND 
(o.CreatedDate >= '2017-03-01' AND o.CreatedDate < '2017-04-01') AND
(o.CreatedDate >= '2017-04-01' AND o.CreatedDate < '2017-05-01') AND
(o.CreatedDate >= '2017-05-01' AND o.CreatedDate < '2017-06-01') AND
(o.CreatedDate >= '2017-06-01' AND o.CreatedDate < '2017-07-01') AND
(o.CreatedDate >= '2017-07-01' AND o.CreatedDate < '2017-08-01') AND
(o.CreatedDate >= '2017-08-01' AND o.CreatedDate < '2017-09-01') AND
(o.CreatedDate >= '2017-09-01' AND o.CreatedDate < '2017-10-01') AND
(o.CreatedDate >= '2017-10-01' AND o.CreatedDate < '2017-11-01') AND
(o.CreatedDate >= '2017-11-01' AND o.CreatedDate < '2017-12-01') AND
(o.CreatedDate >= '2017-12-01' AND o.CreatedDate < '2018-01-01') AND
(o.CreatedDate >= '2018-01-01' AND o.CreatedDate < '2018-02-01') AND
(o.CreatedDate >= '2018-02-01' AND o.CreatedDate < '2018-03-01')

Upvotes: 0

Views: 39

Answers (1)

cdaiga
cdaiga

Reputation: 4939

Try this:

SELECT COUNT(DISTINCT A.CreatedBy) 
FROM
  (SELECT O.CreatedBy, COUNT(DISTINCT DATE_FORMAT(O.CreatedDate ,'%Y-%m')) 
  noActiveMonths
  FROM orders O
  GROUP BY O.CreatedBy) A
WHERE A.noActiveMonths=(TIMESTAMPDIFF(MONTH,
SELECT MIN(A.CreatedDate) FROM orders, 
SELECT MAX(A.CreatedDate) FROM orders)+1);

Upvotes: 1

Related Questions