As mentioned, the "trolleys"/cable cars are a very small part of the public transit system in SF/the Bay Area. We also have buses, trains, subway systems, etc... The cable cars are really just for tourists/sightseeing - expensive and you don't get a transfer, nor are they very accessible. If you want to sight see, then pay $5 to take a cable car from tourist destination to tourist destination (Union Square to Fisherman's Wharf). Otherwise, pay $2 to take a Muni bus/light-rail/subway/train, which will also give you transfers at no extra expense for 2 hours.
BART is another transit system that connects Bay Area cities, mostly SF to the East Bay (Oakland, Berkeley, etc...), as well as a bit south of SF. There's also Caltrain, a commuter train line that runs between SF and San Jose.
As for cost... once you factor in the rental, gas, and parking (if in a busy/metered area - definitely all tourist areas - you'll need to either pay for a meter or a parking garage, and even if in a residential area that requires parking permits for street parking, you'll either have to buy a daily/weekly permit or risk getting a ticket if you're longer than the allowed time), it's not cheap (unless you have tons of people with you). Despite the warnings, there ARE areas that are free (w/ no permit required) and easy to park in the city, but they're also not really the areas most tourists would care to go (more neighborhoody, less 5 story Macy's and wax figure of Angelina Jolie-y). Oh, and bridge toll. It's probably cheaper to stay in SF if that's where you want to be most of the time, and use public transit to get around (BART goes right to the Oakland Coliseum).
That said, having a car has it's advantages. You can head to areas in the Bay Area where public transit is minimal, or a hassle to transfer from system to system (North Bay, coastal part of the peninsula and South Bay, etc...). Also, while parts of SF that tourists rarely tread are easily accessible by bus, it's probably more accessible for a tourist to just use a car (rather than learning the full bus map), and inherently opens up more options for exploring the city.
To the person who said nobody needs a car: perhaps true. There are always other means, including walking. But clearly you've never lived in an area with absolutely no public transit, a couple miles from even the nearest store. But yes, in SF/most anywhere with reasonable public transit, you don't *need* a car (but they can be useful, depending on your situation). As for biking... that's an option, but you do still need to deal with traffic (at least if you're following the laws - though cyclists in SF rarely do, and I wish a painful, miserable death upon them for it).