As has been indicated, if a book (or series of books) is really successful there's a lot of money to be made, particularly if the film rights can be sold as well. Estimates of J K Rowling's wealth vary but £600 million seems to be a reasonable guideline figure.
However, for every one author that makes good money from their writing, there are tens of thousands who make very little at all. Most books (which might have taken several years to write) make less than £1000 for their authors.
Trying to become a successful author is akin to trying to become a successful singer-songwriter. There are only a few people like Ed Sheeran in the world but there are vast numbers of singer-songwriters who feel that they're doing well if they can get a fifty quid pub gig every couple of months. Similarly, most authors are happy enough to sell perhaps just a hundred copies of their work on Amazon.