Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- category || tran_type || Jan_total || feb_total || mar_total
- A || debit || $101 || $201 || $302
- A || credit || $500 || $600 || $200
- 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
- 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;
- SELECT category, YEAR(date), MONTH(date), tran_type, SUM(amounts)
- FROM TableName
- GROUP BY TableName;
- 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;
- 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;
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement