I think fish can make excellent pets so long as you're willing and able to put in the time and effort required to care for them appropriately - just like with any pet. They're great for people renting places that can't have cats, dogs, and the like.
I can't stand background noises that the pumps make so there's no way I could have fish in my house. Perhaps in a pond in the backyard, but it would have to be very well planned in order to provide a suitable habitat. I've always been sad to see fish kept in such tiny glass bowls and aquariums, just not enough space. Goldfish grow to a massive size if allowed the space to grow freely.