I thought Carlos' death in Saints Row 2 was pretty sad, then I let my self fall in to the characters personality and blitzed through the rest of the brotherhood missions to get revenge and kill every single one of them.
I can't really think of a happy death in a story that I've played, apart from Roman in GTA IV, no more strip club for you! I made myself chuckle in Fallout 3 when I evacuated Vault 101, Amanta had a little rant, said if she sees me again, blah, blah, blah, so I pinned her head to the exit with a railway rifle.
I currently can't think of anyone I wanted to die in a video game, so I'll leave it at that.