CODEWITHSUNDEEP
mysqlsqldatabase
AdityaDees
AdityaDees

Reputation: 1037

How can I count distinct months names in a set of date values?

I have table 'Data' and there has two field is Date_date1 and also Data_date2, and i want count it based on month. this my database

Table: Data
Data_date1  Data_date2   
---------------------------------
2019-07-23  2019-01-23
2019-08-23  2019-01-24
2019-08-24  2019-02-23
2019-09-21  2019-07-23
2019-09-22  2019-09-22
2019-09-23  2019-09-23

and i want the results like this one

Month   Count_Date1     Count_Date2
Jan     0               2
Feb     0               1
July    1               1
Aug     2               0
Sep     3               9

Upvotes: 0

Views: 477

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269603

You can use union all and group by:

select month(dte), sum(cnt1), sum(cnt2)
from ((select data_date1 as dte, 1 as cnt1, 0 as cnt2
       from t
      ) union all
      (select data_date2, 0, 1
       from t
      )
     ) dd
group by month(dte);

This shows the month number rather than the month name.

If you want the month name, you would do:

select monthname(dte), sum(cnt1), sum(cnt2)
from ((select data_date1 as dte, 1 as cnt1, 0 as cnt2
       from t
      ) union all
      (select data_date2, 0, 1
       from t
      )
     ) dd
group by monthname(dte), month(dte)
order by month(dte);

Upvotes: 1

Strawberry
Strawberry

Reputation: 33935

Try this:

SELECT MONTH(data_date) m
  ,SUM(d=1) d1
  ,SUM(d=2) d2 
FROM
  (SELECT 1 d, data_date1 data_date FROM my_table
   UNION
   SELECT 2, data_date2 FROM my_table
  ) x
GROUP BY m

Here’s some setup with which to test this query, which produces the desired results:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(data_date1  DATE NOT NULL
,data_date2  DATE NOT NULL
);

INSERT INTO my_table VALUES
('2019-07-23','2019-01-23'),
('2019-08-23','2019-01-24'),
('2019-08-24','2019-02-23'),
('2019-09-21','2019-07-23'),
('2019-09-22','2019-09-22'),
('2019-09-23','2019-09-23');

Upvotes: 1

Related Questions