CODEWITHSUNDEEP
phpmysqlsqldatabase
A.A Noman
A.A Noman

Reputation: 5270

Month wise data fetch from date field in mysql

I have a table name billing and my table data looks like below

id site_name billing_date amount
1 abc 2021-01-15 100
2 abc 2021-02-15 80
3 abc 2021-03-15 120
4 abc 2021-04-15 110
5 abc 2021-05-15 105
6 abc 2021-06-15 90
7 abc 2021-07-15 106
8 abc 2021-08-15 70
9 xyz 2021-01-15 100
10 xyz 2021-02-15 90
11 xyz 2021-03-15 200
12 xyz 2021-04-15 300

My expected result will be like this data

site_name Year January February March Arpil May June July August September October November December
abc 2021 100 80 120 110 105 90 106 70 0 0 0 0
xyz 2021 100 90 200 300 0 0 0 0 0 0 0 0

Moreover site_name,abc month January has only one record, February has only one record same as other months. I have tried the below code but I don't get my expected output

select site_name, YEAR(billing_date) as YearName, monthname(billing_date) as monthName, amount
    from bills
    group by site_name, YEAR(billing_date),monthname(billing_date),amount
    order by site_name;

Please any suggestions on how to get my expected output

Upvotes: 0

Views: 1390

Answers (1)

A.A Noman
A.A Noman

Reputation: 5270

As per the following link of @jarlh I have solved my problem as below code. It is really helpful for me and what I want.

SELECT site_name, YearName 
, SUM(CASE WHEN month =  1 THEN amount END) January      
, SUM(CASE WHEN month =  2 THEN amount END) February      
, SUM(CASE WHEN month =  3 THEN amount END) March      
, SUM(CASE WHEN month =  4 THEN amount END) April      
, SUM(CASE WHEN month =  5 THEN amount END) May      
, SUM(CASE WHEN month =  6 THEN amount END) June      
, SUM(CASE WHEN month =  7 THEN amount END) July      
, SUM(CASE WHEN month =  8 THEN amount END) August      
, SUM(CASE WHEN month =  9 THEN amount END) September      
, SUM(CASE WHEN month =  10 THEN amount END) October   
, SUM(CASE WHEN month =  11 THEN amount END) November      
, SUM(CASE WHEN month = 12 THEN amount END) December   
FROM (SELECT ems_table.electric_bills.* 
    , EXTRACT(YEAR  FROM last_billing_date) YearName              
    , EXTRACT(MONTH FROM last_billing_date) month           
    FROM ems_table.electric_bills) 
    alias_table  GROUP BY site_name, YearName ORDER BY site_name ASC;

Upvotes: 1

Related Questions