SQL query error - need fresh eyes

Question

Well, firstly, I am creating a query to search for MLS data, which was provided in the form of a MySQL database. Therefore, I do not have control over the data format, and therefore I believe that I need to do a lot of casting to get the data in a manageable form. SQL 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 ' DECIMAL(2, 1)) / .5, 
CAST(idx1.full_baths, DECIMAL(2, 1))), DECIMAL(2, 1)) AS b' at line 1

I looked at the error code and it sends me to a reserved page of words, but I can not identify the reserved words.

and now sql

(all fields initially VARCHAR)

SELECT  idx_common.mls_no AS mls_no, 
        CONCAT_WS(" ", idx_common.street_no, idx_common.street_direction, idx_common.street_name) AS address, 
        idx_common.city AS city, 
        idx_common.state AS state, 
        idx_common.total_sqft AS total_sqft, 
        idx_common.asking_price AS price, 
        idx1.bedrooms AS bedrooms, 
        CAST(
            SUM(
                (CAST(idx1.half_baths, DECIMAL(2, 1)) / .5), 
                CAST(idx1.full_bath, DECIMAL(2, 1))
            ), 
            DECIMAL(2, 1)
        ) AS bathrooms, 
        idx1.residential_prop_type AS type, 
        "Listing Agent" AS agent 

FROM (idx_common) 
JOIN idx1 ON idx_common.mls_no = idx1.mls_no 

WHERE `idx_common`.`mls_no` = 'query' 
OR idx_common.zip LIKE '%query%' 
OR idx_common.city LIKE '%query%'

Answer 1

I believe that you do not need here SUM:

SELECT  idx_common.mls_no AS mls_no, 
        CONCAT_WS(" ", idx_common.street_no, idx_common.street_direction, idx_common.street_name) AS address, 
        idx_common.city AS city, 
        idx_common.state AS state, 
        idx_common.total_sqft AS total_sqft, 
        idx_common.asking_price AS price, 
        idx1.bedrooms AS bedrooms, 
        CAST(idx1.half_baths AS DECIMAL(2, 1)) * .5 +
        CAST(idx1.full_bath AS DECIMAL(2, 1)) AS bathrooms, 
        idx1.residential_prop_type AS type, 
        "Listing Agent" AS agent 
FROM    idx_common
JOIN    idx1
ON      idx_common.mls_no = idx1.mls_no 
WHERE   `idx_common`.`mls_no` = 'query' 
        OR idx_common.zip LIKE '%query%' 
        OR idx_common.city LIKE '%query%'

I also changed / 0.5to * 0.5as it seems more appropriate for this request.

3 2 , (3 / 2) + 2= 3.5.

, ?

Answer 2

SUM() . SUM (Cast (...), Cast (...))

, +, SUM (CAST (idx1.half_baths, DECIMAL (2, 1))/.5) + CAST (idx1.full_bath, DECIMAL (2, 1))

SUM . GROUP BY.

Answer 3

I believe the function CASTworks with AS, not with ",". Like this:

CAST(idx1.half_baths AS DECIMAL(2, 1))

You need to replace this with all of your CAST.

Answer 4

Make sure your version of MYSQL> 5.0.8. The DECIMAL type was not added to the CAST function until this version.