Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQLdb return `Decimal` for a `sum` of `INT`

This is the table definition I use to reproduce the issue:

create table test_sum_type 
(
  kind char(1) not null,
  n tinyint not null
);

Test data:

+------+---+                                                                                                                                                                                                    
| kind | n |                                                                                                                                                                                                    
+------+---+                                                                                                                                                                                                    
| A    | 1 |                                                                                                                                                                                                    
| B    | 1 |                                                                                                                                                                                                    
| A    | 2 |                                                                                                                                                                                                    
+------+---+  

Query using MySQLdb:

In [32]: cur.execute("select kind, sum(n) from test_sum_type group by kind")                                                                                                                                    
Out[32]: 2L                                                                                                                                                                                                     

In [33]: cur.fetchall()                                                                                                                                                                                         
Out[33]: (('A', Decimal('3')), ('B', Decimal('1')))                                                                                                                                                             

In [34]: cur.execute("select kind, n from test_sum_type")                                                                                                                                                       
Out[34]: 3L                                                                                                                                                                                                     

In [35]: cur.fetchall()                                                                                                                                                                                         
Out[35]: (('A', 1), ('B', 1), ('A', 2))  

As you can see, the resulting column is a Decimal when I use sum.

I've looked into the source code of MySQLdb, there're only two field types set up to be converted to Decimal by default: DECIMAL and NEWDECIMAL.

What may be the reason of this? Is there any way to check the schema of some temporary table used in a specific query?

like image 696
satoru Avatar asked Oct 16 '25 10:10

satoru


1 Answers

It's by design in MySql.

GROUP BY (Aggregate) Functions
The SUM() and AVG() functions return a DECIMAL value for exact-value arguments (integer or DECIMAL), and a DOUBLE value for approximate-value arguments (FLOAT or DOUBLE). (Before MySQL 5.0.3, SUM() and AVG() return DOUBLE for all numeric arguments.)

If you need to return an INT then CAST it.

SELECT kind, CAST(SUM(n) AS SIGNED) n 
  FROM table1 
 GROUP BY kind

Here is SQLFiddle demo

like image 125
peterm Avatar answered Oct 19 '25 00:10

peterm



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!