CODEWITHSUNDEEP
mysqlsql
manlikeangus
manlikeangus

Reputation: 421

How To SUM, GROUP and SPLIT data in MYSQL Database Based on Dates

Table: Accounting

date_due        date_paid         amount_due     amount_paid    category_type
2012-08-12      2012-08-12        500            450            Income
2012-08-13      2012-08-17        200            300            Expense
2012-09-15      2012-09-13        300            300            Income
2012-09-17      2012-09-16        100            100            Income

How do I Generate a Table Like:

date_paid       IncomeAmountPaid               ExpenseAmountPaid
2012-08         TOTAL INCOME IN AUGUST         TOTAL EXPENSE IN AUGUST
2012-09         TOTAL INCOME IN SEPT.          TOTAL EXPENSE IN SEPTEMBER

Upvotes: 2

Views: 1676

Answers (3)

Ould Abba
Ould Abba

Reputation: 843

u can try this, 

 select Year(date_paid), Month(date_paid),
  sum(case when category_type = 'Income' then amount_paid end) IncomeAmountPaid,
  sum(case when category_type = 'Expense' then amount_paid end) ExpenseAmountPaid
 group by Year(date_paid), Month(date_paid)

Upvotes: 0

Taryn
Taryn

Reputation: 247730

You can use the following, which implements a CASE statement and a GROUP BY:

select date_format(date_paid, '%Y-%m') date_paid,
  sum(case when category_type = 'Income' then amount_paid end) IncomePaid,
  sum(case when category_type = 'Expense' then amount_paid end) ExpensePaid
from accounting 
group by date_format(date_paid, '%Y-%m')

See SQL Fiddle with Demo

Upvotes: 5

Hip Hip Array
Hip Hip Array

Reputation: 4763

How to GROUP

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name

How to SUM

SELECT SUM(column_name) FROM table_name

How to SPLIT

create a split function

CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

then use the function

SELECT SPLIT_STR(string, delimiter, position)

Upvotes: 0

Related Questions