Selecting/casting output as integer in SQL

Question

I'm working on a site that requires me to display a graph of the average number per day of a user input. I have a SQL query already that returns this info to me:

SELECT sum(number)/count(number) as average, date FROM stats WHERE * GROUP BY date

This gives me the result I am looking for, but the result is given with three decimals precision. I want to round of this number. I could do it in PHP or my template engine, of course, but I was curious if there was a way to do this all in the database.

Is there a way to cast an output as an integer (in MySQL)?

@jimmy_keen had no idea that existed! Is there a reason to use it? (better, faster, stronger?) — MrGlass
– MrGlass, Commented Jan 17, 2012 at 14:19
@MrGlass - More concise and avoids possible divide by zero problems in other RDBMSs (AFAIK MySQL returns NULL on a divide by zero anyway) — Martin Smith
– Martin Smith, Commented Jan 17, 2012 at 14:22

Oleg Dok · Accepted Answer · 2013-05-20 17:49:51Z

55

SELECT 
  CAST(sum(number)/count(number) as UNSIGNED) as average, 
  date 
FROM stats 
WHERE * 
GROUP BY date

edited May 20, 2013 at 17:49

answered Jan 17, 2012 at 14:07

Oleg Dok

21.9k4 gold badges48 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Corneliu Over a year ago

-1. If you use "INT" as type you will get an error. The available types for CAST/CONVERT can be seen in the documentation dev.mysql.com/doc/refman/5.0/en/…

Oleg Dok Over a year ago

Thats matters? The approach is answering the Q.

Corneliu Over a year ago

@alex on MySQL 5.5 (latest version on some repositories) it will result in a 1064 error. It might work on some newer versions, although it doesn't seem to be documented

Martin Smith · Accepted Answer · 2012-01-17 14:09:24Z

29

The valid types for a CAST in MySQL are as follows

BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

So you could use

SELECT CAST(sum(number)/count(number) AS UNSIGNED) as average...

Or SIGNED if the SUM part can ever add up to a negative number.

answered Jan 17, 2012 at 14:09

Martin Smith

457k97 gold badges777 silver badges887 bronze badges

Comments

djechlin · Accepted Answer · 2013-06-18 15:23:38Z

8

Use the DIV operator.

mysql> SELECT 5 DIV 2;
    -> 2

Integer division. Similar to FLOOR(), but is safe with BIGINT values. Incorrect results may occur for noninteger operands that exceed BIGINT range.

answered Jun 18, 2013 at 15:23

djechlin

61.1k40 gold badges173 silver badges300 bronze badges

Comments

John Woo · Accepted Answer · 2012-01-17 14:07:31Z

7

how about using MySQL FORMAT Function?

mysql> SELECT FORMAT(12345.123456, 4);
+-------------------------+
| FORMAT(12345.123456, 4) |
+-------------------------+
| 12,345.1235             |
+-------------------------+
1 row in set (0.00 sec)

mysql> SELECT FORMAT(12345.123456, 0);
+-------------------------+
| FORMAT(12345.123456, 0) |
+-------------------------+
| 12,345                  |
+-------------------------+
1 row in set (0.00 sec)

answered Jan 17, 2012 at 14:07

John Woo

265k70 gold badges509 silver badges500 bronze badges

Comments

aF. · Accepted Answer · 2012-01-17 14:07:21Z

1

SELECT convert(int, sum(number)/count(number)) as average,
  date
FROM stats
WHERE * GROUP BY date

or

SELECT 
  CAST(sum(number)/count(number) as INT) as average, 
  date 
FROM stats 
WHERE * 
GROUP BY date

answered Jan 17, 2012 at 14:07

aF.

67k45 gold badges141 silver badges201 bronze badges

Comments

Fasil kk · Accepted Answer · 2021-06-23 08:20:21Z

0

User mysql function round()
Example round(23.33) will return 23 in msyql.

Your example query will be updated to

SELECT round(sum(number)/count(number)) as average, date FROM stats WHERE * GROUP BY date

answered Jun 23, 2021 at 8:20

Fasil kk

2,19717 silver badges27 bronze badges

Collectives™ on Stack Overflow

Selecting/casting output as integer in SQL

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

3 Comments

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related