My wife was recently looking up stuff about Devil worship and the occult, and came across the Wikipedia article on sex magic. In addition to being the hind part of a Red Hot Chili Peppers album title, it’s apparently a way to channel magical energy through sexual intercourse. It’s such an oddly simple name, too. While the article notes that sex magic as such started in the nineteenth century and was popularized by Aleister Crowley, the “see also” section indicates that the basic idea is a lot older than that. There’s some indication that many ancient religions practiced temple prostitution, although there’s recently been some doubt cast on the idea.
The idea was popularized by Herodotus, who claimed that Mesopotamian women were required to have sex with strange men in temples to Inanna/Ishtar (whom he identified with Aphrodite). There’s some possible corroboration for this elsewhere. In the Epic of Gilgamesh, a priestess tames the wild man Enkidu by having sex with him. And there are several laws in the Bible specifically against temple prostitution. I’ve seen it suggested that the prohibitions on male homosexuality pertained primarily, if not entirely, to such practices. Some scholars have suggested that our ideas of temple prostitution were based on the symbolic sacred marriage rituals between kings and priestesses rather than actual intercourse. Indian Tantrism also isn’t quite as big on sex as most Americans think, although some adherents do practice what could be termed sex magic.
Of course, the fact that priests and other religious officials are sometimes required to abstain from sex is really the opposite side of the same coin. It still gives sex spiritual power, but a power with negative results instead of positive ones.
As far as magic in fiction goes, it’s apparently an old idea that wizards, like priests, must remain celibate in order to work their craft. I don’t how it started, but as far as relatively recent fantasy goes it appears in Ursula LeGuin’s Earthsea books, and is spoofed in Terry Pratchett’s Discworld. In the latter, wizards claim that sex would deplete their magical power, but it’s revealed in Sourcery that this is actually just a way to keep them from having children who would be way too powerful. Witches apparently don’t play by the same rules, although they sometimes loosely adhere to the Maiden, Mother, and Crone pattern. Nanny Ogg, who typifies the second type of witch, is known to have been quite sexually active in her younger years.