I feel that there are four main varieties of games in this regard:
Games that are most enjoyable in single-player mode, or do not have multi-player at all. (The Legend of Zelda, Portal, etc.)
Games that are fun in single-player, but are generally more fun to play with friends. (Mario Kart (in...