CODEWITHSUNDEEP
phpmysql
senti
senti

Reputation: 73

Count different days of the same month

I select specific rows from the MySQL database and get the following list:

2017-07-28
2017-07-28
2017-07-28
2017-07-28
2017-07-31
2017-07-31
2017-08-01
2017-08-01
2017-08-02
2017-08-02
2017-08-02
2017-08-03
2017-08-04

I would like to count how many days per month were different, in this case I would like to get the following result:

2017-07: 2 different days
2017-08: 4 different days

I tried to do this with the help of arrays but unsuccessfully.

Upvotes: 2

Views: 135

Answers (2)

Strawberry
Strawberry

Reputation: 33935

E.g.

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
,dt DATE NOT NULL
);

INSERT INTO my_table (dt) VALUES
('2017-07-28'),
('2017-07-28'),
('2017-07-28'),
('2017-07-28'),
('2017-07-31'),
('2017-07-31'),
('2017-08-01'),
('2017-08-01'),
('2017-08-02'),
('2017-08-02'),
('2017-08-02'),
('2017-08-03'),
('2017-08-04');

SELECT DATE_FORMAT(dt,'%Y-%m') yearmonth, COUNT(DISTINCT dt) total FROM my_table GROUP BY yearmonth;
+-----------+-------+
| yearmonth | total |
+-----------+-------+
| 2017-07   |     2 |
| 2017-08   |     4 |
+-----------+-------+

Upvotes: 1

Lukasz Szozda
Lukasz Szozda

Reputation: 175596

Using:

SELECT DATE_FORMAT(date_col, '%Y-%m') AS year_month
      , COUNT(DISTINCT DAY(date_col)) AS cnt
FROM your_table
GROUP BY DATE_FORMAT(date_col, '%Y-%m');

How it works:

  • first create group based on year-month part
  • use aggreagate function COUNT to count occurences in each group
  • DISTINCT to get only different values of day in month

Upvotes: 1

Related Questions