Menu
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)?
555
CAST and CONVERT can be used to convert a string to a number of any data type. For example, you can convert a string to a number of data type INTEGER. TO_DATE converts a formatted date string to a date integer. TO_TIMESTAMP converts a formatted date and time string to a standard timestamp.
SELECT CAST(sum(number)/count(number) AS UNSIGNED) as average... Or SIGNED if the SUM part can ever add up to a negative number. Show activity on this post. Use the DIV operator.
Use the CAST() function to convert an integer to a DECIMAL data type. This function takes an expression or a column name as the argument, followed by the keyword AS and the new data type. In our example, we converted an integer (12) to a decimal value (12.00).
SELECT CAST(sum(number)/count(number) as UNSIGNED) as average, date FROM stats WHERE * GROUP BY date If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
