CODEWITHSUNDEEP
mysqldatabase
user1965826
user1965826

Reputation: 101

How to display monthwise report in mysql

I have created 6 months data in my database which is having column name as productiondate. i have provided 6 months date for this productiondate column. Right now i need to retrieve data for monthwise data from this single productiondate column.

I need to display in the following manner

jan , feb, mar,........dec in seperate columns

Upvotes: 1

Views: 8397

Answers (2)

Minesh
Minesh

Reputation: 2302

You need to use GROUP BY month and generate month wise report, You may also need to use Aggregate functions like SUM, COUNT, AVG based on your requirements.

You can utilize MonthName function to get Month Name from date e.g. 

mysql> SELECT MONTHNAME('1998-02-05');
    -> 'February'

so your query might look like below:

SELECT MONTHNAME(productiondate) as mon, SUM(XYZ), COUNT(*) 
FROM Your_Table_Name
GROUP BY mon

Upvotes: 3

Taryn
Taryn

Reputation: 247680

You did not provide any details on your current table structure or existing query, but it sounds like you are trying to pivot data from rows into columns.

MySQL does not have a pivot function so you will need to replicate it using an aggregate function with a CASE expression. You code would be similar to this:

select otherColumns, 
  max(case when month(productiondate)=1 then value end) Jan,
  max(case when month(productiondate)=2 then value end) Feb,
  max(case when month(productiondate)=3 then value end) Mar,
  max(case when month(productiondate)=4 then value end) Apr,
  max(case when month(productiondate)=5 then value end) May,
  max(case when month(productiondate)=6 then value end) Jun,
  ....
from yourtable
group by otherColumns

You would then replace otherColumns with any other columns that you want included in your final result. These columns would then be included in the GROUP BY. Also you would replace the value with the name of the column that you want displayed under each month.

Upvotes: 3

Related Questions