SQL query erroring - need fresh eyes

Ok, first, I'm building a query to search MLS data that has been provided in the form of a MySQL database, So I don't have control over the data format, and thus I believe I have to do a lot of casting to get the data in a manageable form. The SQL error is being thown.

#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

Looked up the error code and it sends me to a reserved words page, but I can't identify any reserved words.

and now the sql

(all fields are natively 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_com开发者_Go百科mon.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%'

I believe you don't need SUM here at all:

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.5 to * 0.5 since it seems to be more appropriate for this query.

If an apartment has 3 half bathrooms and 2 full bathrooms, this query will output (3 / 2) + 2 = 3.5 bathrooms.

Is it what you wanted?

SUM() takes a single argument. You have SUM( Cast(...), Cast(...) )

I think you meant to do +, not SUM (CAST(idx1.half_baths, DECIMAL(2, 1)) / .5) + CAST(idx1.full_bath, DECIMAL(2, 1))

SUM adds all values of the column in the whole table. You can only use it in a GROUP BY query.

I believe that the function CAST works with AS, not a ",". Like this:

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

You need to replace that on all your CASTs.

Ensure that your MYSQL version is > 5.0.8. The DECIMAL type wasn't added to the CAST function until this version.