Mysql Yearly Report according to Categories with month name in columns

Question

1.00/5 (1 vote)

See more:

I have 2 tables revenue and expense

Table: Revenue
------------------------------------------------------
transactionID | transactionDate | Branch | TotalAMount
------------------------------------------------------
1 | 2013-01-01 | MC | 23900
------------------------------------------------------
2 | 2013-01-02 | MC | 12500
------------------------------------------------------
3 | 2013-02-11 | MC | 1735
------------------------------------------------------
4 | 2013-02-15 | MC | 9000
------------------------------------------------------
5 | 2013-03-01 | MC | 1100
------------------------------------------------------
6 | 2013-04-21 | MC | 45690
------------------------------------------------------
7 | 2013-05-01 | MC | 9900
------------------------------------------------------
..... and Revenue lists goes down ....

Table : expenserecord
------------------------------------------------------
id |transactionID | Category| Item| Amount
------------------------------------------------------
1 | 1 | Refreshment | 1500
------------------------------------------------------
2 | 1 | Patrol | 2000
------------------------------------------------------
3 | 2 | Cargo | 350
------------------------------------------------------
4 | 3 | Advertisement | 1200
------------------------------------------------------
..... and ExpenseRecord lists goes down ....

my question is how to get desired output mentioned below

1. Give me 12 months Expense report Category wise. (It may look like below)

Category | Jan | Feb | March
----------------| ------ | -------- | -------
Cargo | 350 | 0 | 0
Refreshment | 1500 | 0 | 6374
Advertisement | 0 | 1200 | 0

..... and product lists goes down ....

Posted 12-Jul-13 15:10pm

Hammad Raza

Updated 13-Jul-13 8:33am

v2

Add a Solution

Comments

Sergey Alexandrovich Kryukov 12-Jul-13 22:07pm

Sorry, not a question.
—SA

2 solutions

Solution 1

This may not be the best answer but at least you can get an idea on how to work on
your report, and not using pivot.

SQL

SELECT A.[Category],
       isnull(SUM([JANUARY]),'') as [JANUARY],
       isnull(SUM([FEBRUARY]),'') as [FEBRUARY],
       isnull(SUM([MARCH]),'') as [MARCH],
       isnull(SUM([APRIL]),'') as [APRIL],
       isnull(SUM([MAY]),'') as [MAY],
       isnull(SUM([JUNE]),'') as [JUNE],
       isnull(SUM([JULY]),'') as [JULY],
       isnull(SUM([AUGUST]),'') as [AUGUST],
       isnull(SUM([SEPTEMBER]),'') as [SEPTEMBER],
       isnull(SUM([OCTOBER]),'') as [OCTOBER],
       isnull(SUM([NOVEMBER]),'') as [NOVEMBER],
       isnull(SUM([DECEMBER]),'') as [DECEMBER]
FROM (SELECT EXPENSERECORD.Category [Category],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'January' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JANUARY],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'February' THEN SUM(EXPENSERECORD.AMOUNT) END AS [FEBRUARY],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'March' THEN SUM(EXPENSERECORD.AMOUNT) END AS [March],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'April' THEN SUM(EXPENSERECORD.AMOUNT) END AS [APRIL],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'May' THEN SUM(EXPENSERECORD.AMOUNT) END AS [MAY],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'June' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JUNE],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'July' THEN SUM(EXPENSERECORD.AMOUNT) END AS [JULY],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'August' THEN SUM(EXPENSERECORD.AMOUNT) END AS [AUGUST],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'September' THEN SUM(EXPENSERECORD.AMOUNT) END AS [SEPTEMBER],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'October' THEN SUM(EXPENSERECORD.AMOUNT) END AS [OCTOBER],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'November' THEN SUM(EXPENSERECORD.AMOUNT) END AS [NOVEMBER],
             CASE WHEN DATENAME(month, REVENUE.transactionDate) = 'December' THEN SUM(EXPENSERECORD.AMOUNT) END AS [DECEMBER]
      FROM REVENUE
      LEFT JOIN EXPENSERECORD ON REVENUE.transactionID =  EXPENSERECORD.transactionID
      GROUP BY  REVENUE.transactionDate, EXPENSERECORD.Category) A
WHERE A.Category IS NOT NULL
GROUP BY A.Category

SQL

Posted 12-Jul-13 16:35pm

berrymaria

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Hammad Raza · Accepted Answer · 2013-07-13T00:57:00

SELECT
er.category `Category`,
SUM(IF(transactionDate between '2013-01-01' and '2013-01-31', r.totalamount, 0)) Jan,
SUM(IF(transactionDate between '2013-02-01' and '2013-02-31', r.totalamount, 0)) Feb,
SUM(IF(transactionDate between '2013-03-01' and '2013-03-31', r.totalamount, 0)) Mar,
SUM(IF(transactionDate between '2013-04-01' and '2013-04-31', r.totalamount, 0)) Apr,
SUM(IF(transactionDate between '2013-05-01' and '2013-05-31', r.totalamount, 0)) May,
SUM(IF(transactionDate between '2013-06-01' and '2013-06-31', r.totalamount, 0)) Jun,
SUM(IF(transactionDate between '2013-07-01' and '2013-07-31', r.totalamount, 0)) Jul,
SUM(IF(transactionDate between '2013-08-01' and '2013-08-31', r.totalamount, 0)) Aug,
SUM(IF(transactionDate between '2013-09-01' and '2013-09-31', r.totalamount, 0)) Sept,
SUM(IF(transactionDate between '2013-10-01' and '2013-10-31', r.totalamount, 0)) Oct,
SUM(IF(transactionDate between '2013-11-01' and '2013-11-31', r.totalamount, 0)) Nov,
SUM(IF(transactionDate between '2013-12-01' and '2013-12-31', r.totalamount, 0)) Dece

from revenue as r
LEFT JOIN expenserecord er ON R.transactionID=ER.transactionID

GROUP BY
er.category

Mysql Yearly Report according to Categories with month name in columns

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Mysql Yearly Report according to Categories with month name in columns

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us