Building Select statement...

[vb89]

I'm trying to create a select statement that takes the max of the ot.possession column and divides it by 2 and puts that value in the football_schedule table as the column quarter...Right now when i run the query there is no output, any ideas?


SELECT *
FROM customer_data.cd_football_game_scoring t, customer_data.cd_football_schedule s
WHERE s.quarter =(select(max(count(t.ot_possession) /2)) from customer_data.cd_football_game_scoring t group by t.game_code)
AND t.game_code = 814533
AND s.game_code = 814533
AND t.scored = 'Y'

[Phill Pafford]

Does you sub query return more than one value?


SELECT(MAX(COUNT(t2.ot_possession) /2))
FROM customer_data.cd_football_game_scoring AS t2
GROUP BY t2.game_code


Might need a WHERE condition in here as well


SELECT *
FROM customer_data.cd_football_game_scoring AS t, customer_data.cd_football_schedule AS s
WHERE s.quarter =(
SELECT(MAX(COUNT(t2.ot_possession) /2))
FROM customer_data.cd_football_game_scoring AS t2
WHERE t2.game_code = 814533 /* <-- Just a Guess */
GROUP BY t2.game_code) /* <-- The group by will return multi rows without a where clause */
AND t.game_code = 814533
AND s.game_code = 814533
AND t.scored = 'Y'