Aeris' death was the only sad moment in gaming that made me cry. In fact, I had accidentally flipped open the strategy guide in GameStop (when it was called Babbages) and landed on that page and THEN I cried XD I was quite young at the time, though.
Other moments that are sad but didn't actually make me cry? Well, I'm sure there are more but the big one is the whole "revelation" scene in Silent Hill 2. The "stair of fire" scene is also sad. I won't tell you guys why, go play it!
The apartment scene in Silent Hill 3 is pretty sad, too...
Actually, the whole series is sad XD Of course, what horror game isn't?
The Gardevoir storyline in Mystery Dungeon is quite sad.
The ending of Super Paper Mario.
The ending of E-102 Gamma's storyline in Sonic Adventure. The end of Sonic Adventure 2 is sad too.
However, perhaps the saddest ending I've ever seen was the end of the gameboy Zelda game "Link's Awakening". I always get depressed when I finish that one XD