CODEWITHSUNDEEP
sql-servert-sql
wseide
wseide

Reputation: 19

TSQL for Batting Average formula

I have a question that states:

Using a BETWEEN clause, find all players with a Batting Average between .0.300 and 0.3249. The query should return the Full Name (NameGiven (NameFirst) NameLast), YearID, Hits, At Bats and Batting Average sorted by descending batting average.

SELECT 
    people.playerid, 
    (nameFirst +' ( '+ nameGiven + ' ) ' + nameLast) AS 'Full Name', 
    yearID, H, AB, 
    CONVERT(decimal(5, 4), h * 1.0 / AB) AS [Batting Average] 
FROM 
    People, Batting
WHERE 
    Batting Average BETWEEN .300 AND 0.3249
ORDER BY
    Batting Average DESC

I get a red underline in the WHERE clause for Batting Average.

Why is it not recognizing the Batting Average column in the Select?

What am I doing wrong?

Upvotes: 0

Views: 202

Answers (1)

trincot
trincot

Reputation: 350760

The reason for the error is that you did not use the square brackets around the field name like you did in the SELECT clause: [Batting Average].

However, you cannot reference aliases from the SELECT clause in a WHERE clause. The reason is that the WHERE clause is evaluated before the expressions in the SELECT clause are evaluated. On the other hand, you can reference such an alias in the ORDER BY clause. But don't forget those brackets.

In the WHERE clause you'll have to use the formula on the original fields again.

You should also avoid division by zero. So better check that AB is not zero.

Finally, you did not specify a join for the two tables, so your query would produce a Cartesian product. I'll assume that the Batting table has a foreign key called playerid.

So taking all those points into consideration, this should be a better query:

SELECT     people.playerid, 
           (nameFirst +' ( '+ nameGiven + ' ) ' + nameLast) AS 'Full Name', 
           yearID, 
           H,
           AB, 
           Convert(decimal(5,4), h*1.0/AB) as [Batting Average] 
FROM       People
INNER JOIN Batting ON Batting.playerid = People.playerid
WHERE      AB <> 0
       AND h*1.0/AB BETWEEN .300 AND 0.3249
ORDER BY   [Batting Average] DESC

Upvotes: 3

Related Questions