mySQL Query for summing amount in columns (months)

I have data in the following format:

date (mm/dd/yyyy), desc (detail desc), category (a,b,c), tran_type (debit, credit), amo开发者_JAVA百科unt spent (amounts).

I would like to get the data in the following format:

category  ||  tran_type   ||   Jan_total  ||  feb_total  ||  mar_total  
A         ||  debit       ||   $101       ||  $201       ||  $302  
A         ||  credit      ||   $500       ||  $600       ||  $200  

This query should give you result required.

SELECT category
  ,tran_type
  ,SUM(IF(month(date) = 1,ABS(amount),0)) as jan_total
  ,SUM(IF(month(date) = 2,ABS(amount),0)) as feb_total
  ,SUM(IF(month(date) = 3,ABS(amount),0)) as mar_total
  ,SUM(IF(month(date) = 4,ABS(amount),0)) as apr_total
  ,SUM(IF(month(date) = 5,ABS(amount),0)) as may_total
  ,SUM(IF(month(date) = 6,ABS(amount),0)) as jun_total
  ,SUM(IF(month(date) = 7,ABS(amount),0)) as jul_total
  ,SUM(IF(month(date) = 8,ABS(amount),0)) as aug_total
  ,SUM(IF(month(date) = 9,ABS(amount),0)) as sep_total
  ,SUM(IF(month(date) = 10,ABS(amount),0)) as okt_total
  ,SUM(IF(month(date) = 11,ABS(amount),0)) as nov_total
  ,SUM(IF(month(date) = 12,ABS(amount),0)) as dec_total
 FROM transactions
 WHERE YEAR(date) = '2011'
 GROUP BY category, tran_type

Don't forget to filter on year if you don't want to run in to trouble.

And what about years? Do you want Janury column to add transactions for Jan 2011 with Jan 2010?

I'm assuming you would need the Year column adding in separate or use a WHERE clause for that, so included both options for the example:

Basically you will need to produce twelve sub queries to achieve that and the SQL will look messy and is not the normal method of accomplishing this:

SELECT category, YEAR(date), tran_type,

    (SELECT SUM(amounts) FROM TableName s1 WHERE YEAR(t.date)=YEAR(s1.date) AND MONTH(s1.date)=1 AND t.category=s1.category AND t.tran_type=s1.tran_type) AS 'Jan_Total',

    (SELECT SUM(amounts) FROM TableName s2 WHERE YEAR(t.date)=YEAR(s2.date) AND MONTH(s2.date)=2 AND t.category=s2.category AND t.tran_type=s2.tran_type) AS 'Feb_Total',

     ....REPEAT ABOVE 2 LINES FOR EACH MONTH

FROM TableName t
WHERE t.date>'2011-01-01'
GROUP BY t.category, YEAR(t.date), t.tran_type;

as mentioned the above is not exactly elegant and a better solution would be to use the following SQL and format the data in the presentation layer to the user:

SELECT category, YEAR(date), MONTH(date), tran_type, SUM(amounts)
FROM TableName
GROUP BY TableName;

Hope that helps.

For a single month (January 2011) you can use:

SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type;

assuming you have an index on at least date then this should be reasonably fast. A better index would be (date, category, tran_type) as this would help with the grouping too.

If you want to combine multiple months in the format you've described you could assemble a number of these queries as subqueries something like:

SELECT jan.category, jan.tran_type, jan.total, feb.total FROM
    (SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2011-02-01" GROUP BY category, tran_type) AS jan,
    (SELECT category, tran_type, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-02-01" AND date<"2011-03-01" GROUP BY category, tran_type) AS feb
 WHERE jan.category = feb.category
 AND jan.tran_type = feb.tran_type;

Though I suspect the easiest way of fetching the data that you want (though not very efficient and not in quite the format you described) would be:

SELECT category, tran_type, MONTH(date) AS theMonth, SUM(amount_spent) AS total FROM myTable WHERE date>="2011-01-01" AND date<"2012-01-01" GROUP BY category, tran_type, theMonth;

This use of the MONTH() function is far from optimal but if you don't run the query that often could be the way to go.