CODEWITHSUNDEEP
mysqlcasting
super9
super9

Reputation: 30111

MySQL casting from decimal to string

Why doesn't the CAST work in this MySQL query?

SELECT  MAX(Signups) AS Max, 
        MIN(Signups) AS Min,
        CAST(ROUND(AVG(Signups),2) AS VARCHAR(3)) AS Avg
FROM
(
    SELECT COUNT(1) AS Signups,
    DATE_FORMAT(JoinDate, "%Y-%m-%d") AS Date
    FROM users
    GROUP BY Date
) z

Why am I getting this error?

#1064 - You have an error in your SQL syntax; check the manual that corresponds 
to your MySQL server version for the right syntax to use near 
'VARCHAR(3)) AS Avg FROM ( ' at line 2

Upvotes: 5

Views: 25867

Answers (2)

Lightness Races in Orbit
Lightness Races in Orbit

Reputation: 385144

This may be due to MySQL bug #34564: CAST does not accept varchar type.

Try casting to a different type, like this:

CAST(ROUND(AVG(Signups),2) AS CHAR(3)) AS Avg

Upvotes: 10

super9
super9

Reputation: 30111

Changing VARCHAR to CHAR solves the problem

Upvotes: 1

Related Questions