I don’t have kids and I don’t know anything about sports. If you continue reading after those disclosures, I’ll offer a perspective anyway, since you put this out to the internet for comment.

There isn’t really a way you could have put this to your son that would be taken well, it’s evidently sensitive for him and despite your intentions it’ll feel like a tragic monent. It’s just hard news. Whether it’s right to break that to him, well I’m not sure but I think maybe you’re putting too much emphasis on this one interaction like it was your one shot and there was a definitive right it wrong way to do it. What will matter most is more likely to be what you do generally moving forward. You may have your doubts about his ability in his chosen path and perhaps they’re well founded but you can still encourage him and be rooting for him whilst gently suggesting having backup options in times when he appears uncertain. If you consistently do all you can to help in whatever way you can with whatever choices he makes, then if they don’t work out and he has to abandon that dream, he’ll at least know you supported him all throughout despite your concern and that should count for a lot. If somehow he ends up unexpectedly rocketing to success in football he’ll also remember you’d been there all along encouraging and assisting. It’s ok to counsel against putting his eggs all in one basket, but just don’t push it, you must respect his choice whatever it ends up being and he there to help pick up the pieces if those choices don’t make him happy.

Much like with football fans, you support your team by just showing up to every match and cheering on. Perhaps he didn’t like the uncomfortable dose of reality today but so long as you are consistently a positive and helpful force he’ll hopefully come to appreciate what you’ve been trying to do for him.