There are some songs which are never sung sober. "Nellie Dean" is one. So is any song beginning "As I was a walking ..." In the area around Ankh-Morpork, the favoured air is "A Wizard's Staff Has A Knob On The End".
A simple way to indicate that a person is drunk is to have them sing a drunken song. It may be bawdy or weepily sentimental, but it indicates inebriation even before you notice the slurred phrasing and lurching walk.
This was such a recurring trope in old British comedies that anyone who was drunk sang Nellie Dean and anyone singing Nellie Dean was drunk. It was useful cultural shorthand in the days before you could show a grown man pissing or puking in public.
See Ode To Intoxication for songs about getting drunk.
Many Ealing comedies and similar, for example in The Bargee, Harry H Corbett is able to get out of his girlfriend's bed before her large, violent father gets home from the pub because he's singing Nellie Dean loudly.
The aforementioned "A Wizard's Staff..." on Discworld, and the once heard never forgotten Hedgehog Song. (Fans have worked out full sets of lyrics for both - the Hedgehog Song has, in canon, at least seventeen verses.)
The full title of "the hedgehog song" is The Hedgehog Can Never Be Buggered At All. That should give you an idea why it is never sung while sober.
Male characters in the Belgariadnever sing while sober. We're never told what exactly they're singing, but it tends to scare off any birds in the vicinity.
The last thing he remembered was joining in a singsong with a group of drunken tourists at Il-Eruk’s Tavern. He’d sung the song about the turnip fish.
In Gone with the Wind, when Gerald O'Hara is drunk, he sings a song called "Peg in a Low-Backed Car".
Played for Laughs in Sourcery when the Four Horsemen of the Apocralypse get drunk and start singing "We are poor little lambs who have lost our way"; indeed, they are so drunk that they forget most of the words not to mention horses.trivia
This used to be a common Drunken Song in English media. It may have been originally been meant as a Genius Bonus, since it's the chorus from "Gentleman Rankers," an extremely dark poem by Rudyard Kipling that deals with alcoholism and losing one's social position due to spending too much on various pleasures.
Happens several times in Star Trek: Deep Space Nine, e.g. Miles O'Brien and Julian Bashir singing "Jerusalem", or Worf singing Yet Another Klingon Battle Song with Miles or some grizzled Klingon veteran he's trying to cozy up to.
All the Klingon songs are either opera or this. Both types are spectacularly gory.
A TNG episode had Picard and his older brother get drunk and sing after they have a big fist fight.
In Babylon 5, Garabaldi sings "Show Me The Way To Go Home" on one occasion when drunk.
The crew of Red Dwarf (minus Kryten), having been celebrating Rimmer's deathday, return to the ship singing "Show Me the Way to Go Home" while tracing three-dimensional esses in space. Later in the same episode, Rimmer sings "Someone to Watch Over Me" in a very plaintive manner. He then degenerates into high-pitched, tuneless humming.
One episode of Black Books has Bernard visit some friends and singing a very Irish drinking song to their young son. The outtakes reveal that not only did Dylan Moran improvise it, he improvised a whole bunch of them.
Pretty much anything by The Pogues, but most particularly "A Pair of Brown Eyes".
The Wild Rover is an older song performed by the Pogues among many others. The lyrics make it an Ode To Sobriety, but it's almost always sung ironically.
Their celtic-rock brethern in general fall in this trope, particularly Dropkick Murphy's "Shipping Up to Boston" and Flogging Molly's "Drunken Lullabies."
The Dropkick Murphys also have the aptly titled "Kiss me, I'm Shitfaced"
If you want more folk, The Ramblin Rover by Silly Wizard probably should never be sung sober.
The band Gaelic Storm is taking the drinking song tradition to a new generation.
Forty-Seven Ginger-Headed Sailors.
Tom Waits' The Piano Has Been Drinking (Not Me) is a tragicomic take on the drunken song. The pianist denies his own inebriation, anthropomorphizing various objects to pin the blame on. "The carpet needs a haircut..."
The Irish folk song "Seven Drunken Nights", most famously performed by the Dubliners. Gets very bawdy at the end...
Done in thisSongs To Wear Pants To song where the instructions were to "[Not] sing the lyrics. Shout them out in a drunken sort of way."
Bondo has a song called "Fuck you I'm Drunk", which is probably more well known than they are.
The Dead Kennedys "Too Drunk To Fuck". Nouvelle Vague's cover took this to the next level: the lead singer actually sounded like she was hammered when they recorded it.
Damien Rice's "Cheers, Darlin'" is a perfect example of this trope. Rice often makes a show of drinking while performing this song live.
hide's solo song "Drink or Die." Also taken to the next level in that he was likely drunk most of the time he performed it, and sort of a "Funny Aneurysm" Moment / Harsher in Hindsight in that alcohol would later be a part of why he did actually die.
A lot of Steely Dan songs mention some kind of alcoholic beverage, or sound like they're either from the point of view of a morose drunk or about the results of a bender.
Bob Dylan has a few, although the most obvious is "Just Like Tom Thumb's Blues": it has a kind of drunken vibe from the very beginning, but the final verse confirms it:
I started out on burgundy But soon hit the harder stuff Everybody said they'd stand behind me When the game got rough But the joke was on me There was nobody even there to bluff I'm going back to New York City I do believe I've had enough.
Joni Mitchell's song "Talk to Me", about begging for conversation from someone not willing to speak, gains entirely new context from its opening line:
There was a moon and a streetlamp I didn't think I drank such a lot Till I pissed a tequila anaconda the full length of the parking lot.
In Henry IV Part II, Falstaff and assorted cronies get drunk and sing a song with a nonsensical refrain.
And in Othello, Iago leads the singing in the Cyprus officers' mess. It's debatable whether he himself is drunk or just pretending, but both the songs are classic drunken efforts. King Stephen was a worthy peer, his breeches cost him half a crown ...
In Twelfth Night, Sir Toby Belch and Sir Anderew Ague-Cheek sing drunken songs in the middle of the night until interrupted by the Countess's manservant Malvolio.
In Mafia II, Joe and Eddie at one point drunkenly sing along to Dean Martin's "Return to Me" on the radio.
In Jak II: Renegade, Daxter offers to "help" Tess behind the bar and ends up getting totally wasted within the span of about thirty seconds, leading to him singing drunkenly for a little bit while Jak and Krew discuss the next mission.
Betrayal In Antara has three different drinking songs in it, each of which has four verses. You can find people singing one verse of one song in many of the taverns/inns in the game.