asked on

Summaries not adding up - am I doing something wrong or is this a bug?

I'm doing reports of transactions, summarizing daily and monthly. I noticed though that they do not tally and something is wrong with the output for the daily summaries. See below.

mysql> select date(trx_date), count(id) trx_count, coins/1000 as amount from wallet_trx group by date(trx_date);
+----------------+-----------+---------+
| date(trx_date) | trx_count | amount |
+----------------+-----------+---------+
| 2011-10-10 | 1 | 0.1000 |
| 2011-10-11 | 2 | 1.1110 |
| 2011-10-22 | 73 | 0.8400 |
| 2011-10-23 | 75 | 0.8400 |
| 2011-10-24 | 33 | 0.8400 |
| 2011-10-25 | 136 | 5.0000 |
| 2011-10-26 | 96 | 5.6000 |
| 2011-10-27 | 105 | 0.8400 |
| 2011-10-28 | 105 | 5.6000 |
| 2011-10-29 | 86 | 2.8000 |
| 2011-10-30 | 62 | 0.8400 |
| 2011-10-31 | 81 | 2.8000 |
(output snipped, just showing October)

mysql> select month(trx_date), sum(coins)/1000 amount from wallet_trx group by month(trx_date);
+-----------------+-----------------+
| month(trx_date) | amount |
+-----------------+-----------------+
| 10 | 5142.3770 |
(output snipped)

You don't have to add the first results to know that no way they will add up to 5142.3770

Further investigating:

mysql> select month(a.trx_date), sum(a.amount) from (select trx_date, coins/1000 as amount from wallet_trx group by trx_date) a group by month(a.trx_date);
+-------------------+---------------+
| month(a.trx_date) | sum(a.amount) |
+-------------------+---------------+
10 | 27.2110 |
(output snipped)

I can't find anything wrong with my SQL for daily summary. Can you find something I'm doing wrong or is there a bug in my MySQL installation? I've tried on two builds - 5.1.41-3ubuntu12.10 (Ubuntu) and 5.1.57-rel12.8-log Percona Server with XtraDB (GPL), Release 12.8, Revision 232

Attached is dumpfile for the table. Thanks.

Dave Baldwin

This:

select month(trx_date), sum(coins)/1000 amount from wallet_trx group by month(trx_date);

is selecting the month of October for All of the Years in the database. I think you need to add a WHERE to limit it to the current year of interest.

http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_month

johanntagle

ASKER

Thanks DaveBaldwin, but you will see from the sql dump that the table only has data from Oct 2011 to February 2012. There's no overlap of years.

You will also notice that my last query above I made a monthly summary from the output of the daily summary. That should have tallied with the SQL that does the monthly summary directly.

Still, your comment made me realize that my migration scripts (something related) will bomb out should somebody run them after September 2012, so I need to adjust that. Thanks. (UPDATE: turns out I did have this covered, but thank you still =) )

ASKER CERTIFIED SOLUTION

Aaron Tomosky

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

johanntagle

ASKER

That did it! Yeah I wish MySQL would just give an error for such cases. Thanks!

Dave Baldwin

Glad you found it. I didn't see an attachement. ?

johanntagle

ASKER

My apologies DaveBaldwin, I must have missed actually uploading it.