You should have known that there was nothing to gain by telling him what you did. Kids that age are smart enough to realize that if they aren't being selected to the local all-star team, it's because they're not an all-star. If they go to football camp and they aren't one of the best people at the camp, they'll realize that they're not very likely to go pro. But you decided to make it your business at a time when you didn't need to, and that makes you a jerk.
You said that you're just being objective and realistic, right, but you decided to tell your son your opinion, and not someone else. If you were actually trying to be objective, you would have told everyone on the team what you thought about their potential. Of course that would be really rude, which is the point.
What you could have done is what many other people have mentioned in the comments. Something about how there's no guarantee that anyone can make it pro, or how long they'll last if they do, because random injuries can end your career, and the median length of a professional footballer isn't very long anyway, so there's still the rest of life to live.
There's no way your dad could have known what was going to happen. There's no way people around you could have known that you were in a career path that wasn't going to work out well for you. Nobody can guarantee the future like that.
The other thing is that even if you're working in STEM, to follow up with your example, there are thousands of different jobs that all feel totally different to people working them. It's quite possible that you could initially hate the field, then make some lateral shift, and find a position that is halfway decent. Here again, nobody knows what's going to be good for you.
If you want responsible career advice, it's quite simple. Because there aren't guarantees, you might want to develop several different skill sets, so that you're in a better position to deal with unknown future changes. If you think you can learn how to do one simple thing and then have 45 years of happiness doing it, flip a coin and hope luck is on your side.