CODEWITHSUNDEEP
javamysql
Phani
Phani

Reputation: 35

Get the months name for the a particular date range

I have a date range '2015/01/01' to '2015/12/31'. My database has date,device_id columns of table "device".

From this date range i want to display the month-name and № of unique device_ids in that month. E.g, my database having date and devie_id are two columns

date        device_id

2015-01-01     1
2015-01-20     1
2015-03-01     1
2015-03-01     3
2015-04-01     2
2015-06-01     3
2015-08-01     4
2015-09-01     1

expected result

Month   device_count
January      1
March        2
April        1
June         1
August       1
September    1

How to bring the month Name? I am using java class to retrive the data.

Below is the image for clear idea of the result which i needed

enter image description here

Upvotes: 3

Views: 70

Answers (3)

1000111
1000111

Reputation: 13519

You can try the following query:

SELECT
DATE_FORMAT(`date`,"%M") AS 'Month',
COUNT(DISTINCT device_id) AS device_count
FROM YOUR_TABLE
GROUP BY DATE_FORMAT(`date`,"%Y-%m");

Please check this SQL FIDDLE DEMO

Result:

You will get output like below:

Month   device_count
January      1
March        2
April        1
June         1
August       1
September    1

Note:

%M Month name (January-December)

%Y Year, numeric, four digits

%m Month, numeric (00-12)

You can check MYSQL DATE_FORMAT

Caution:

The reason behind grouping by year,month pair is to make sure that the same months across different years don't get summed up in the same slot. The following example might give you an hint:

SELECT MONTH(STR_TO_DATE('2016-04-14','%Y-%m-%d'));

Result: 4

SELECT MONTH(STR_TO_DATE('2014-04-14','%Y-%m-%d'));

Result: 4

Upvotes: 3

Priyanshu
Priyanshu

Reputation: 881

select month(date),count(distinct device_id) from table_name group by month(date);

Upvotes: 3

Priyamal
Priyamal

Reputation: 2989

SELECT device_id, 
    DATE_FORMAT(date, '%b') AS AddDate
    FROM device

this might work for you but you should reffer this DATE_FORMAT to see how DATE_FORMAT works.

Upvotes: 1

Related Questions