What causes the "rotting fish smell"?
The "fishy" odor that you're familiar with is brought about by a whole bunch of compounds, and not any single one. Then again, if we were to narrow this down a bit, we could say that simple nitrogen compounds are the main culprits.  

But suppose we want to blame only a single compound for the delightfully pungent odor of rotting fish, and we couldn't be bothered with the nuances of olfactory physiology or food chemistry to go through a list of compounds running into the hundreds (if not thousands). In that case, we can pin the blame on trimethylamine (TMA).



With regard to fish, the TMA is a result of the degradation of trimethylamine oxide (TMAO) present in the body fluids (blood, lymph, excreta) of the fish.

Just so you don't get the wrong idea, TMAO is present in the body fluids of all vertebrates ̶I̶ ̶c̶a̶n̶ ̶t̶h̶i̶n̶k̶ ̶o̶f̶, not just fish (albeit in much lower concentrations, which is why you don't mistake ground chicken for tuna).



Now why is TMAO more abundant in fish?

Ever tried to pickle a cucumber (or anything for that matter)? Notice that the cucumber shrivels up over time? It's losing water to the pickling fluid by a process known as exosmosis: the water is drawn out from a less concentrated environment (cucumber's tissue) to a more concentrated one (pickling fluid).

 Disclaimer: As user @lly pointed out in the comments, this particular bit has the potential to cause a bit of confusion. The inside of a cell (the cytoplasm I mean) is technically an aqueous colloidal solution (the pickling fluid is an aqueous solution as well). In the context of aqueous solutions, "concentration" is an indication of how much solute is present, not water. So in this context, if something is more concentrated, it has less water in it 


  


Now the same thing is going to want to happen to a fish in salt water, which isn't so nice from the fish's perspective.

To counter the outflow of water, a fish has a significantly higher concentration of the familiar metabolite urea in its body fluids (as compared to terrestrial vertebrates). This establishes a situation where the fish's body fluids have roughly the same "concentration" as the salt water it swims in (the more correct term here would be "osmolarity", in place of "concentration")

That urea, if left to its own devices, will seek to destabilize  macromolecular structures and inhibit cellular functions; the TMAO present alongside the urea helps counteract those detrimental effects.

Of course, going by this logic, saltwater fish should have a much stronger "fish odor" than freshwater fish (which is certainly the case).

Lets not forget the classic kitchen tip: Rubbing fish with something acidic like vinegar, b̶a̶t̶t̶e̶r̶y̶ ̶a̶c̶i̶d̶ or lemon juice helps reduce the odor (the TMA degrades under acidic conditions).



Also, some of you might know that a fish-like smell is observed in rancid food. That, apparently, is the result of the oxidation of omega-3 fatty acids.