Syntax error in my query

Databases
1

Hello, can I please have assistance finding out what’s wrong with my query. I’m new to sql and have been trying to pin point the error but I’m unable to. Please help!


SELECT
    player_id,
    player_first_name,
    player_last_name, game_id,
    round((accurate_shots / total_shots) * 100, 0 ) AS accuracy_pct
FROM
    (
        SELECT
            player_id,
            player_first_name,
            player_last_name,
            game_id,
            COUNT(shot_no)
            OVER(PARTITION BY player_id, game_id) AS total_shots,
            SUM((
                CASE
                    WHEN service_line_accuracy = 'Y' THEN
                        1
                    ELSE
                        0
                END
            ))
            OVER(PARTITION BY player_id, game_id) AS accurate_shots
        FROM
            (
                SELECT
                    player_id,
                    player_first_name,
                    player_last_name,
                    game_id,
                    shot_no,
                    CASE
                        WHEN player_x_value > 0
                             AND shot_x_value BETWEEN serviceline.mid_point AND serviceline.end_range
                             AND shot_y_value BETWEEN sideline.start_range AND sideline.end_range THEN
                            'Y'
                        WHEN player_x_value < 0
                             AND shot_x_value BETWEEN serviceline.mid_point AND serviceline.start_range
                             AND shot_y_value BETWEEN sideline.start_range AND sideline.end_range THEN
                            'Y'
                        ELSE
                            'N'
                    END AS service_line_accuracy
                FROM
                    shots       s,
                    player      p,
                    serviceline sel,
                    sideline    sil
                WHERE
                    s.player_id = p.player_id
            ) shot_level_data
    ) player_game_level_data;

image
image1920×2560 474 KB

2

error at the left parenthesis on line 14 ?

OVER(PARTITION BY ...

this suggests window functions are not supported

error near the keyword CASE on line 33 ?

WHEN player_x_value > 0

this looks okay, though

actually, your query looks okay to me, except for the unnecessary parentheses inside the SUM()

and also this nonsense here –

FROM
shots s,
player p,
serviceline sel,
sideline sil
WHERE
s.player_id = p.player_id

do those 3rd and 4th tables have only 1 row each? if not, you’re getting a big cross join there

4

Actually no , okay thanks I’ll make corrections

5

Think that one is just a case of a missing , at the end of line 13, which would have been spotted had the “leading commas convention” been used

6

a comma at the end of line 13 would result in this syntax –

COUNT(shot_no) , OVER(PARTITION BY ...

and that of course is invalid

but well played with the leading comma comment!! i’m glad others are aware of it

7

@ss.butterfly what SQL server are you using (including the version number of that server)?

8

based on the error messages, it’s Microsoft SQL Server

@ss.butterfly, please run this –

SELECT @@VERSION AS myversion
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.