troubleshooting Question

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

Avatar of johanntagle
johanntagleFlag for Philippines asked on
MySQL ServerSQL
6 Comments1 Solution365 ViewsLast Modified:
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.
ASKER CERTIFIED SOLUTION
Aaron Tomosky
Director, SD-WAN Solutions

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 1 Answer and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros