CODEWITHSUNDEEP
mysql
user2455079
user2455079

Reputation: 418

MySQL SUM Amount per month and group by caterory

I have table: User nvarchar(30) Date datetime Total decimal(8,2)

And i need to get data per month like this: enter image description here

I can get it this way (without month columns):

SELECT User, MONTHNAME(FROM_UNIXTIME(Date)) as month, SUM(Total) as total FROM `wp_banking_expenses` group by month,User

But i need it to make as on image above...

Upvotes: 0

Views: 48

Answers (2)

Fahmi
Fahmi

Reputation: 37493

use conditional aggregation using case when expression

select user,max(case when month='January' then total end) as Jan,
max(case when month='February' then total end) as Feb,
max(case when month='March' then total end) as Mar,
max(case when month='April' then total end) as Apr,
max(case when month='May' then total end) as May,
max(case when month='June' then total end) as June,
----
from
(
SELECT User, MONTHNAME(Date) as month, SUM(Total) as total
FROM `wp_banking_expenses` 
group by month,User
)A group by user

Upvotes: 1

D-Shih
D-Shih

Reputation: 46269

You can try to use condition aggregate function.

SELECT  User, 
        SUM(CASE WHEN MONTHNAME(Date) =  'January' THEN Total END) as Jan, 
        SUM(CASE WHEN MONTHNAME(Date) =  'February' THEN Total END) as Feb,
        SUM(CASE WHEN MONTHNAME(Date) =  'March' THEN Total END) as Mar,
        SUM(CASE WHEN MONTHNAME(Date) =  'April' THEN Total END) as Apr,
        SUM(CASE WHEN MONTHNAME(Date) =  'May' THEN Total END) as May,
        SUM(CASE WHEN MONTHNAME(Date) =  'June' THEN Total END) as JUN,
        SUM(CASE WHEN MONTHNAME(Date) =  'July' THEN Total END) as JUL, 
        SUM(CASE WHEN MONTHNAME(Date) =  'August' THEN Total END) as AUG,
        SUM(CASE WHEN MONTHNAME(Date) =  'September' THEN Total END) as SEP,
        SUM(CASE WHEN MONTHNAME(Date) =  'October' THEN Total END) as OCT,
        SUM(CASE WHEN MONTHNAME(Date) =  'November' THEN Total END) as NOV,
        SUM(CASE WHEN MONTHNAME(Date) =  'December' THEN Total END) as DEC
FROM `wp_banking_expenses` 
group by User

Upvotes: 1

Related Questions