CODEWITHSUNDEEP
mysqlcount
user3101582
user3101582

Reputation: 57

How to get count of records month-wise from mysql table?

I have a mysql table points like this

id | points | orderid | date
1  | 10     | 1234    | 2014-01-14
2  | 20     | 4321    | 2014-02-04
3  | 10     | 1234    | 2014-01-13

I want to get count of records for unique orderids

like if i want to fech total records for month of jan then result will be

1 not 2 because i need to fetch records for unique orderids

Upvotes: 1

Views: 23583

Answers (3)

anand pandey
anand pandey

Reputation: 1

You can use substr function of MySQL to cut down the complete date string into year and month and group the resulting string. 

select substr(createdTime,1,7) as month,COUNT(1) from TABLENAME where {CONDITION} group by month order by 1;

Upvotes: 0

FRIEDRICH M. SAM
FRIEDRICH M. SAM

Reputation: 11

SELECT MONTHNAME(date_reg) M, YEAR(date_reg) Y, COUNT(MONTHNAME(date_reg)) T 
FROM users 
WHERE date_reg BETWEEN DATE_ADD(DATE_SUB(DATE_SUB(LAST_DAY(NOW()), INTERVAL 
   DAY(LAST_DAY(NOW())) DAY), INTERVAL 5 MONTH), INTERVAL 1 DAY) AND   
   DATE_SUB(LAST_DAY(NOW()), INTERVAL DAY(LAST_DAY(NOW())) DAY) 
GROUP BY M 
ORDER BY Y, M DESC;

Upvotes: 1

Barmar
Barmar

Reputation: 780787

Use the DISTINCT modifier in the COUNT() function.

SELECT YEAR(date) AS y, MONTH(date) AS m, COUNT(DISTINCT orderid)
FROM yourTable
GROUP BY y, m

Upvotes: 16

Related Questions