Movies:
The Effect of Gamma Rays on Man-in-the-Moon Marigolds (also a novel)
8 Heads in a Duffle Bag
The Incredibly Strtange Creatures Who Stopped Living and Became Mixed-Up Zombies
“Mother Hitton’s Littul Kittons” is another great Cordwainer Smith story title.
But the ultimate is James Blish’s “Faust Aleph-Null”, the title under which the story was serialized in If. It was expanded a bit into a book titled “Black Easter”, “Faust Aleph-Null” being relegated to a sub-title.
Bloodhound Gang had a song called “You’re Pretty When I’m Drunk.”
Tom Robbins’ Fierce Invalids Home from Hot Climates.
I bought that book strictly because of the title, and it was awesome.
For song titles, it’'s hard to beat Jimmy Buffett’s The Weather Is Here, Wish You Were Beautiful.
The recent book** An Arsonist’s Guide to Writers’ Homes in New England** by Brock Clarke is worth mentioning.
I once picked up a book at a library book sale called The Natural Science of Stupidity.
I moved shortly thereafter and lost it. I may still have it in storage somewhere.
Catch-22
Portrait of the Artist as a Young Man
The Sorrows of Young Werther
War and Peace
I know these are all famous books, but look at those titles,
Holy shit that sounds awesome! 
I nominate the hitchhikers guide to the galaxy.
The reason I picked up my first Terry Pratchett book was because The Fifth Elephant sounded goofy enough to be interesting.
Jaguars Ripped My Flesh
Actually, the title is great because it’s a humdrum description of the book’s contents.
From Daniel Pinkwater:
The Snarkout Boys and the Avocado of Doom
Fat Men from Outer Space
The Hoboken Chicken Emergency
Not to mention their albums
Punk in Drublic
White trash, two heebs and a bean
Let’s see, I had a list somewhere…
*
Hot Wireless Sets, Aspirin Tablets, The Sandpaper Sides of Used Matchboxes, And Something That Might Have Been Castor Oil*
*
The Big Space Fuck*
If All Men Were Brothers, Would You Let One Marry Your Sister?
Olson’s Standard Book of British Birds (Expurgated Version).
The one without the gannet.
In the TSIA category is a classic: Kidnapped! Exclamation point mandatory. If it weren’t such a crackerjack story, the title might be less impressive; but it’s sort of the 19th century equivalent of Snakes on a Plane.
The Woman with a Worm in Her Head: And Other True Stories of Infectious Disease
I admit I bought it in part to see my SO’s reaction, who at the time was working at the bookstore where I bought it and rang me up. To my dismay, he just raised his eyebrow and thumbed through it a bit.
Ooh, that reminds me of another one, a themed short-story anthology with a title that fucking sells: The Thackery T. Lambshead Pocket Guide to Eccentric and Discredited Diseases. (It’s got some great stories in it, too!)
I picked that book up at a secondhand market purely because of the title.
Other pop-science books seem to have picked up on the trick since.
The Woman Who Loved an Octopus: And Other Saints’ Tales is a book by a vague acquaintance of mine, and I claim some credit for persuading her to use that title rather than other more pedestrian titles.
Never underestimate just how big an impact a good title can have.
My only published work (in a collection) was due to a moment of inspiration about the title. I thought ‘A Clockwork Orange is Not the Only Fruit,’ (with capitals and everything), and there appeared before me an essay comparing different approaches to authorship, with the polar opppsites of Jeanette Winterson and Anthony Burgess. Thank the Lord for an appreciation of puns. I would never have completed my MA without it.
I love ‘The Jewish-Japanese Sex and Cook Book and How to Raise Wolves by Jack Douglas.’ The title, that is. What’s the book about?
*A Dark Traveling
To Die in Italbar
Ancient of Days
The Book of Night With Moon*