We help IT Professionals succeed at work.

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

johanntagle
johanntagle asked
on
361 Views
Last Modified: 2012-02-12
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.
Comment
Watch Question

Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
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
Top Expert 2012

Author

Commented:
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 =) )
Director, SD-WAN Solutions
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Top Expert 2012

Author

Commented:
That did it!  Yeah I wish MySQL would just give an error for such cases.  Thanks!
Dave BaldwinFixer of Problems
CERTIFIED EXPERT
Most Valuable Expert 2014

Commented:
Glad you found it.  I didn't see an attachement.  ?
Top Expert 2012

Author

Commented:
My apologies DaveBaldwin, I must have missed actually uploading it.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.