CODEWITHSUNDEEP
mysqlsqlint
MrGlass
MrGlass

Reputation: 9262

Selecting/casting output as integer in SQL

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)?

Upvotes: 35

Views: 121830

Answers (6)

Fasil kk
Fasil kk

Reputation: 2187

  1. User mysql function round()
  2. 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

Upvotes: 0

djechlin
djechlin

Reputation: 60768

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.

Upvotes: 8

Oleg Dok
Oleg Dok

Reputation: 21756

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

Upvotes: 54

Martin Smith
Martin Smith

Reputation: 453067

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.

Upvotes: 29

John Woo
John Woo

Reputation: 263703

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)

Upvotes: 7

aF.
aF.

Reputation: 66697

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

Upvotes: 1

Related Questions