| 1 |
3 |
peter |
/* Here are some sample MySQL queries you can try once you've extracted
|
| 2 |
|
|
all of the transactions into a MySQL table. */
|
| 3 |
|
|
|
| 4 |
|
|
/* Query to show total spending by 'item' (i.e. the payee) in descending order */
|
| 5 |
|
|
|
| 6 |
|
|
SELECT item,sum(amount) AS total FROM transactions WHERE amount < 0 AND item <> '' GROUP BY item ORDER BY total;
|
| 7 |
|
|
|
| 8 |
|
|
/* Query to show total spending by year. */
|
| 9 |
|
|
|
| 10 |
|
|
SELECT year(date),sum(amount) AS total FROM transactions WHERE amount < 0 AND item <> '' GROUP BY year(date);
|
| 11 |
|
|
|
| 12 |
|
|
/* Query to show total spending by year and month. */
|
| 13 |
|
|
|
| 14 |
|
|
SELECT date_format(date,"%Y-%m") AS yearmonth,sum(amount) AS total FROM transactions WHERE amount < 0 AND item <> '' GROUP BY yearmonth;
|
| 15 |
|
|
|
| 16 |
|
|
/* Query to show total spending for a single "item" (i.e. payee) by year and month. */
|
| 17 |
|
|
|
| 18 |
|
|
SELECT date_format(date,"%Y-%m") AS yearmonth,sum(amount) AS total FROM transactions WHERE amount < 0 AND item = 'CASA MIA' GROUP BY yearmonth
|