Nested selects when dealing with calculated columns

Question

I'm trying to write a nested select statement that returns a single row that emulates the batting average from this site https://www.espn.com/mlb/team/stats/_/name/hou/season/2005/seasontype/2/table/batting/sort/avg/dir/desc. I encountered a divide by zero error that I used (Case when AB=0 then 1 else AB end) for "at bats" which works but I get 6 rows instead of 1.

select namefirst + namelast as 'Name', (h\*1.0)/(Case when AB=0 then 1 else AB end) as 'BA' FROM vwPlayersBatting
WHERE  'BA' = (SELECT MAX('BA')
FROM vwPlayersBatting
WHERE teamID = 'HOU' and yearID = 2005)
and teamID = 'HOU' and yearID = 2005 and AB \> 400

I'm basing the top statement off similar statements like the one below that have worked such as for homeruns

 select namefirst + namelast as 'Name', HR FROM vwPlayersBatting
 WHERE HR = (SELECT MAX(HR)
 FROM vwPlayersBatting
 WHERE teamID = 'HOU' and yearID = 2005)
 and teamID = 'HOU' and yearID = 2005

I can get away with doing that on the top line but on the where line it says invalid column name. — hudg, Dec 08 '22 at 22:43
I see now BA is a calculated field. Can't reference a calculated field name in WHERE clause in the same query it is generated. Would have to repeat the calculation in the WHERE clause everywhere BA is referenced. — June7, Dec 08 '22 at 22:50
Which RDBMS are you using? MySQL 5.x, MySQL 8, PostgreSQL, SQL Server, SQLite, etc, etc... They each have different syntax. Also, please give a concrete example with concrete data, to demonstrate your problem. [Why should I provide a Minimal Reproducible Example for a very simple SQL query?](https://meta.stackoverflow.com/q/333952) — MatBailie, Dec 08 '22 at 22:59
You "fixed" the error by comparing the string `'BA'` to another string, which is also `'BA'`. So, your where clause does nothing *(`'BA'` is a string literal, not a column name)*. In SQL the WHERE clause is applied before the SELECT clause is calculated, so you can't refer to the newly calculated column in the WHERE clause. The best/correct syntax will depend on which DBMS you're using. — MatBailie, Dec 08 '22 at 23:12
Thank you I am quite new at this. I am using SQL Server Management Studio (SSMS) — hudg, Dec 08 '22 at 23:15
Which version of Microsoft SQL Server? *(Not the version of SSMS, which is just a client you use to communicate with the server.)* — MatBailie, Dec 08 '22 at 23:17

score 0 · Answer 1 · answered Dec 08 '22 at 23:30

You can order by a calculated column, and then restrict the results to the top 1 (with ties in as multiple batters are equal top).

SELECT TOP 1 WITH TIES
  namefirst + namelast   AS name, 
  (h*1.0)/NULLIF(AB, 0)  AS BA
FROM
  vwPlayersBatting
WHERE
      teamID = 'HOU'
  AND yearID = 2005
  AND AB     > 400
ORDER BY
  2 DESC

Nested selects when dealing with calculated columns

1 Answers1