CODEWITHSUNDEEP
mysqlsqldistinct
phpJs
phpJs

Reputation: 452

mysql count distinct days in a month

in mysql table1 i have column dateofreport and there are few records per day e.g.

dateofreport 
2013-05-31
2013-05-31
2013-05-30
2013-05-30
2013-05-30
2013-05-29
2013-04-31
2013-04-31
2013-04-31
2013-04-02

I want to find out how many distinct days there are for a month,
so result should be: 2013-May : 3, 2013-April : 2

i can do distinct days in all table: SELECT COUNT( DISTINCT dateofreport ) FROM table1 or distinct months but i don't know how to group it by months.

SELECT DISTINCT 
DATE_FORMAT(`dateofreport`,'%Y-%M') as months 
FROM table1

Upvotes: 1

Views: 1685

Answers (3)

Bill Karwin
Bill Karwin

Reputation: 562260

SELECT EXTRACT(YEAR_MONTH FROM dateofreport) AS ym,
    COUNT(DISTINCT dateofreport) AS count
FROM table1
GROUP BY ym

The only reason I use EXTRACT() is that it's ANSI standard SQL, IIRC. Both types of function are likely to cause temporary tables.

Upvotes: 4

Kenny
Kenny

Reputation: 1553

SELECT DATE_FORMAT(dateofreport, '%Y-%M') AS months,
    COUNT(DISTINCT dateofreport) AS count
FROM table1
GROUP BY months

Upvotes: 0

DevlshOne
DevlshOne

Reputation: 8457

SELECT 
    EXTRACT(YEAR_MONTH FROM dateofreport) AS distinctmy,
    COUNT(DISTINCT DAYOFYEAR(dateofreport)) AS uniquedays 
FROM
    table1 
GROUP BY distinctmy

OUTPUT

enter image description here

Upvotes: 0

Related Questions