It seems to have been the editor who proposed the title, and the author didn't like it much.
The original suggestion seems to have come from Fitzgerald's editor and friend, Maxwell Perkins:
I always thought that "The Great Gatsby" was a suggestive and effective title, -- with only the vaguest knowledge of the book, of course. But anyway, the last thing we want to do is divert you to any degree, from your actual writing, and if you let matters rest just as they are now, we shall be perfectly satisfied. The book is the thing, and all the rest is inconsiderable beside it.
-- Maxwell Perkins to F. Scott Fitzgerald, 16 Apr 1924 (source)
When the author first sent the book to Perkins, he titled it The Great Gatsby but was unsure of this title:
Under separate cover I'm sending you my third novel: The Great Gatsby.
I have an alternative title: Gold-hatted Gatsby. After you've read the book let me know what you think about the title.
-- F. Scott Fitzgerald to Maxwell Perkins, 27 Oct 1924 (source)
Before his editor even had the chance to reply to this letter, he sent another, amended, version and changed the title to Trimalchio in West Egg:
Trimalchio in West Egg. The only other titles that seem to fit it are Trimalchio and On the Road to West Egg. I had two others, Gold-hatted Gatsby and The High-bouncing Lover, but they seemed too light.
-- F. Scott Fitzgerald to Maxwell Perkins, 7 Nov 1924 (source)
Perkins responded as follows:
Now deal with this question: various gentlemen here don't like the title,—in fact none like it but me. To me, the strange incongruity of the words in it sound the note of the book. But the objectors are more practical men than I. Consider as quickly as you can the question of a change.
-- Maxwell Perkins to F. Scott Fitzgerald, 18 Nov 1924 (source)
After persuasion from both his wife and his editor, Fitzgerald agreed to the title The Great Gatsby. But months later, even when the book was about to be published, he was still trying to make last-minute changes to the title:
On 7 March , Fitzgerald cabled Perkins to ask if it was too late to change the novel’s title because he wanted to revert to “Gold-Hatted Gatsby” or “Trimalchio.” Perkins replied on 9 March: “Title change would cause bad delay and confusion.” And on the nineteenth Fitzgerald cabled again: CRAZY ABOUT TITLE UNDER THE RED WHITE AND BLUE STOP WHART WOULD DELAY BE. By then it was too late.
The book was published on 10 April 1925 under the title The Great Gatsby.
But why "Great"?
As in this similar question about an unrelated work, there may be an element of irony or even sarcasm in the title. Perhaps Gatsby really is as bad as you say, and the title refers to him as "Great" as an ironic way of pointing up how un-Great he actually is.
But there are other interpretations. For instance, this article considers the question of what makes Gatsby "great" and makes the case that he actually is great, as a character and arguably as a person too. Readers might admire him or find his story relatable:
Perhaps Gatsby is no more than a simple man who lives, loves, and dies. But perhaps he is much more than that – a hero for all dreamers, one who stands for the survival of his dreams even in the face of unconquerable adversity, and one who dies tragically, an honorable yet empty man, with an army of faithful readers mourning his defeat in death but unceasingly admiring his disposition in life. Perhaps, just maybe, it is what he is fighting for that everyone can relate to. When it comes down to it, Gatsby is fighting to chase a love that is slipping too quickly into his past for him to catch. We, as the readers, know that Gatsby’s desire for Daisy’s love is a hopeless case, yet we want so badly for him to be happy; it is this paradox that brings us all together in support of Gatsby. It is what makes us hate Daisy when she cries over the shirts. It is what makes us love him when he puts his pride aside to hide in the bushes to make sure she is alright. It is what makes us sick when Gatsby’s body is floating dead in his pool. It is what makes us feel as though we are there at his funeral, mourning the loss of a close friend.
Gatsby's success story is also one that could well be called "great", and which Americans in particular might admire and identify with:
[Fitzgerald] created Jay Gatsby to embody the American dream. That unique American ability to go from rags to riches. A dream that is the epitome of all dreams, and that all people have dreamt at one time or another: The poor boy or the broke soldier having the very very rich girl, and rising to the class of the rich and famous. [...] in a mere 3 years, he went from nothing to owning one of the largest houses in New York speaking to the most powerful people around, and throwing parties that every important person in the Us attended. This is the American Dream.
And more than that: as well as being financially successful, Gatsby has a personal life which is arguably dominated by self-sacrifice. What you describe as "quite delusional in his love affairs" could also be described as a man who will do anything for love, even to the extent of sacrificing his own life.
Even though F. Scott Fitzgerald could have stopped there, he did not feel that James Gatz had truly become great. He points out that Gatsby, unlike everyone else, achieved this greatness for love. When one person selflessly gives themselves, sacrifices themselves, sacrifices everything they have, and even gives their life for someone else; they are greater than anyone. Gatsby's pursuit of Daisy, to win her love, is the dream he lives for, and hope sustains him. Daisy is Gatsby's version of the American Dream, the love of his life, the perfect housewife, the ultimate status, "the king's daughter, the golden girl" (however all of these qualities are just Gatsby's idealization of her after dreaming about her for five years) and he is willing to sacrificing everything to obtain her. James Gatz lived his entire life to love Daisy. When he shows her the stuff in his house, he's showing her the house he has created for her. He doesn't really care for any of this - it's all done specifically for her. Even the parties stopped when she didn't like them. [...]
In the end, he says that he will tell the police that he was driving, he waits outside her house like a gallant knight, and finally takes a bullet for her so that she may live on. Jay Gatsby has lived and created all that he has in the name of love and the name of Daisy, not James Gatz or Jay Gatsby.
The last comment from Gatsby is about Daisy coming to him, and Nick responds by stating that Gatsby is better then all of them. So Gatsby dies for love and for the people. Moreover he is great in all desires and all his dids. [sic]