I have a this table:

mysql> describe transaxtions;
+---------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| account_id | int(11) | NO | MUL | | |
| category_id | int(11) | NO | MUL | | |
| created_at | datetime | YES | | NULL | |
| updated_at | datetime | YES | | NULL | |
| note | varchar(255) | YES | MUL | | |
| description | varchar(255) | YES | MUL | | |
| memo | varchar(255) | YES | MUL | | |
| amount | float | YES | MUL | 0 | |
| merchant_id | int(11) | NO | | | |
+---------------+--------------+------+-----+---------+----------------+


And I need summary info count,total,average for an account over the last 12 months...

I currently have this:

SELECT
MONTH(transaxtions.date) as month,
DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date,
LAST_DAY(transaxtions.date) as end_date,
sum(transaxtions.amount) as total_amount,
avg(transaxtions.amount) as average_amount,
count(transaxtions.id) as total_count
FROM
transaxtions
WHERE (transaxtions.date >= '2006-07-01 00:00:00')
GROUP BY MONTH(transaxtions.date)
ORDER BY transaxtions.date


This is returning good data for the months that have transactions. But some months have no transactions and hence get no result row returned.

This has to be a common problem, but searching around has not helped much.

As an added bonus, I'd like a column in the result set for how many months ago this data is for. Right now I have the month column, but that is for the month of the year.

Thanks in advance experts @!

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'select 5 union all select 6 union all select 7
select 8 union all selec' at line 9

I am not familiar with the union all syntax, will look it up.

ok, found the missing "union all" statements, but this still does not return data for empty rows...

SELECT m as month
, DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
, LAST_DAY(transaxtions.date) as end_date
, sum(transaxtions.amount) as total_amount
, avg(transaxtions.amount) as average_amount
, count(transaxtions.id) as total_count
FROM ( select 1 as m union all
select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 10 union all
select 11 union all select 12 ) as months
LEFT OUTER
JOIN transaxtions
ON MONTH(transaxtions.date) = m
WHERE transaxtions.date >= '2006-07-01'
GROUP
BY m
ORDER
BY start_date


here is what I get now...


mysql> SELECT m as month
-> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
-> , LAST_DAY(transaxtions.date) as end_date
-> , sum(transaxtions.amount) as total_amount
-> , avg(transaxtions.amount) as average_amount
-> , count(transaxtions.id) as total_count
-> FROM ( select 1 as m union all
-> select 2 union all select 3 union all select 4 union all
-> select 5 union all select 6 union all select 7 union all
-> select 8 union all select 9 union all select 10 union all
-> select 11 union all select 12 ) as months
-> LEFT OUTER
-> JOIN transaxtions
-> ON MONTH(transaxtions.date) = m
-> WHERE transaxtions.date >= '2006-07-01'
-> GROUP
-> BY m
-> ORDER
-> BY start_date;
+-------+------------+------------+-------------------+------------------+-------------+
| month | start_date | end_date | total_amount | average_amount | total_count |
+-------+------------+------------+-------------------+------------------+-------------+
| 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
| 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
| 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
| 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
| 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
+-------+------------+------------+-------------------+------------------+-------------+

no, don't bother looking up UNION ALL, that's not your problem

your problem is you're on a really ancient version of mysql

(you're not still running windows 3.1, are you?

run the following query:and then change your main query to:

I am no version 5.0.38 (latest is 5.0.41), not too ancient.

And no, I am not on 3.1 !!!

me@feisty:~/workspace/rails_toucan_200$ mysql --version
mysql Ver 14.12 Distrib 5.0.38, for pc-linux-gnu (i486) using readline 5.2

new results below...still no data for "empty" rows...and this is a nicer way to get the month integers !!!

mysql> insert into months values
-> (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ;
Query OK, 12 rows affected (0.00 sec)
Records: 12 Duplicates: 0 Warnings: 0

mysql> SELECT m as month
-> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
-> , LAST_DAY(transaxtions.date) as end_date
-> , sum(transaxtions.amount) as total_amount
-> , avg(transaxtions.amount) as average_amount
-> , count(transaxtions.id) as total_count
-> FROM months
-> LEFT OUTER
-> JOIN transaxtions
-> ON MONTH(transaxtions.date) = m
-> WHERE transaxtions.date >= '2006-07-01'
-> GROUP
-> BY m
-> ORDER
-> BY start_date;
+-------+------------+------------+-------------------+------------------+-------------+
| month | start_date | end_date | total_amount | average_amount | total_count |
+-------+------------+------------+-------------------+------------------+-------------+
| 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
| 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
| 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
| 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
| 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
+-------+------------+------------+-------------------+------------------+-------------+
5 rows in set (0.01 sec)

yeah, i figured out it wasn't an older version of mysql after the query ran, however, your error message in post #3 is exactly what you get if your version of mysql doesn't support subqueries

anyhow, the reason you 're not getting all the months is because of the WHERE clause, which i should've spotted sooner

change the word WHERE to AND

woohooo, data

Last thing, I really want these in oldest to newest order, sorting on the date column does not work as that is null for the empty rows. Sorting on month only works at the end of the year !

I'd really like a months ago column to sort on, and to not have to hardcode the start date...



mysql> SELECT m as month
-> , DATE_FORMAT(transaxtions.date,'%Y-%m-01') as start_date
-> , LAST_DAY(transaxtions.date) as end_date
-> , sum(transaxtions.amount) as total_amount
-> , avg(transaxtions.amount) as average_amount
-> , count(transaxtions.id) as total_count
-> FROM ( select 1 as m union all
-> select 2 union all select 3 union all select 4 union all
-> select 5 union all select 6 union all select 7 union all
-> select 8 union all select 9 union all select 10 union all
-> select 11 union all select 12 ) as months
-> LEFT OUTER
-> JOIN transaxtions
-> ON MONTH(transaxtions.date) = m
-> AND transaxtions.date >= '2006-07-01'
-> GROUP
-> BY m
-> ORDER
-> BY start_date;
+-------+------------+------------+-------------------+------------------+-------------+
| month | start_date | end_date | total_amount | average_amount | total_count |
+-------+------------+------------+-------------------+------------------+-------------+
| 12 | NULL | NULL | NULL | NULL | 0 |
| 8 | NULL | NULL | NULL | NULL | 0 |
| 9 | NULL | NULL | NULL | NULL | 0 |
| 1 | NULL | NULL | NULL | NULL | 0 |
| 10 | NULL | NULL | NULL | NULL | 0 |
| 2 | NULL | NULL | NULL | NULL | 0 |
| 11 | NULL | NULL | NULL | NULL | 0 |
| 3 | 2007-03-01 | 2007-03-31 | 3700.30054092407 | 11.420680681864 | 324 |
| 4 | 2007-04-01 | 2007-04-30 | 4901.86039757729 | 13.071627726873 | 375 |
| 5 | 2007-05-01 | 2007-05-31 | 4289.21094655991 | 10.512771927843 | 408 |
| 6 | 2007-06-01 | 2007-06-30 | 5173.60045814514 | 12.742858271293 | 406 |
| 7 | 2007-07-01 | 2007-07-31 | -2104.87997531891 | -29.646196835478 | 71 |
+-------+------------+------------+-------------------+------------------+-------------+
12 rows in set (0.11 sec)

are you sure you even want the month number?

try this --

Your right, I don't really need the month...

I get this sql error now...

Boy, I really need to brush up on my sql here, thanks alot for helping me out here so patiently.



mysql> SELECT start_date
-> , LAST_DAY(start_date) as end_date
-> , sum(transaxtions.amount) as total_amount
-> , avg(transaxtions.amount) as average_amount
-> , count(transaxtions.id) as total_count
-> FROM (
-> SELECT DATE_SUB(
-> DATE_ADD(LAST_DAY(current_date), INTERVAL 1 DAY)
-> INTERVAL 1 MONTH) as start_date
-> FROM ( select 1 as m union all
-> select 2 union all select 3 union all select 4 union all
-> select 5 union all select 6 union all select 7 union all
-> select 8 union all select 9 union all select 10 union all
-> select 11 union all select 12 ) as months
-> ) as start_dates
-> LEFT OUTER
-> JOIN transaxtions
-> ON transaxtions.date BETWEEN start_date
-> AND LAST_DAY(start_date)
-> GROUP
-> BY start_date
-> ORDER
-> BY start_date;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
'INTERVAL 1 MONTH) as start_date
FROM ( select 1 as m union all
' at line 9

oops, my bad, please insert a comma right in front of INTERVAL 1 MONTH

Ok, you have really made my day here. I never realized the magic you could do with datetime functions.

I am sure this post will help others in the future.

Is there anyway to reward people on this forum ???

Ok, one more...I added the missing comma...now I am trying to limit the transaxtions to those for a particular account via the WHERE clause I added below...of course it does not like my syntax, and it did not seem write when I typed it in...




SELECT start_date
, LAST_DAY(start_date) as end_date
, sum(transaxtions.amount) as total_amount
, avg(transaxtions.amount) as average_amount
, count(transaxtions.id) as total_count
FROM (
SELECT DATE_SUB(
DATE_ADD(LAST_DAY(current_date), INTERVAL 1 DAY),
INTERVAL m MONTH) as start_date
FROM ( select 1 as m union all
select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all
select 8 union all select 9 union all select 10 union all
select 11 union all select 12 ) as months
) as start_dates
LEFT OUTER
JOIN transaxtions
ON transaxtions.date BETWEEN start_date
AND LAST_DAY(start_date)
WHERE transaxtions.account_id = 21
GROUP
BY start_date
ORDER
BY start_date;

To be more clear, the syntax appears to be fine, but I no longer get my much wanted empty rows !