title: sqlquery not working giving error
body: <p>I to write a query for the book database.  The question is to List each author's total earnings in 1995-2000 in descending order.     </p>
  
  <pre><code>SELECT DISTINCT b.author,b.earnings as TOTAL_EARNING
  FROM BookAuthor as b lEFT JOIN
       BookPublish
       on b.book=BookPublish.book
  WHERE (b.earnings=COUNT(b.earnings)) AND (year BETWEEN 1995 AND 2000)
  ORDER BY b.earnings desc ;
  </code></pre>
  
  <p>ERor: Invalid use of group function.</p>
  
  <pre><code>create table BookPublish( 
        book varchar(255) ,
        year int,
        publisher varchar(255),
        price int,
        num int,
        PRIMARY KEY (book)
      );
  
      //INSERT INTO BookPublish VALUES('Math-1',1999,'Mcgraw',120,5000);
      //INSERT INTO BookPublish VALUES('Physics',2001,'Matt Woods',85,2500);
      //INSERT INTO BookPublish VALUES('Chemistry',1996,'McGraw',99,2300);
      //INSERT INTO BookPublish VALUES('Biology',1999,'Krishna',75,2000);
      //INSERT INTO BookPublish VALUES('Economics',2000,'Maze',125,3000);
      //INSERT INTO BookPublish VALUES('Engineering',1994,'ABC',180,8000);
      //INSERT INTO BookPublish VALUES('Medicine',1997,'ABC',180,9000);
  
  
      create table BookAuthor( 
        book varchar(255) ,
        author varchar(255) ,
        earnings int,
        PRIMARY KEY (book,author)
      );
  
  
      //INSERT INTO BookAuthor VALUES('Math-1','Chris,Norman',12000);
      //INSERT INTO BookAuthor VALUES('Physics','Matt Woods',4000);
      //INSERT INTO BookAuthor VALUES('Chemistry','John Bayer',8200);
      //INSERT INTO BookAuthor VALUES('Biology','Craig S',3000);
      //INSERT INTO BookAuthor VALUES('Economics','Manohar',17000);
      //INSERT INTO BookAuthor VALUES('Engineering','Sterling',13050);
      //INSERT INTO BookAuthor VALUES('Medicine','Craig S',10000);
  
      create table BookReview( 
        book varchar(255) ,
        reviewer varchar(255) ,
        score int,
        PRIMARY KEY (book,reviewer)  ,
        FOREIGN KEY (book) REFERENCES BookPublish(book)
      );
  
  
      //INSERT INTO BookReview VALUES('Math-1','Paul Gray',7);
      //INSERT INTO BookReview VALUES('Math-1','Daphne Merkin',8);
      //INSERT INTO BookReview VALUES('Physics','Daphne Merkin',6);
      //INSERT INTO BookReview VALUES('Chemistry','John',9);
      //INSERT INTO BookReview VALUES('Biology','Jack',10);
      //INSERT INTO BookReview VALUES('Economics','Paul Gray',7.5);
      //INSERT INTO BookReview VALUES('Engineering','Paul Gray',8);
      //INSERT INTO BookReview VALUES('Engineering','Daphne Merkin',7);
  
  
      create table BookReference( 
        book varchar(255) ,
        bookreferenced varchar(255) ,
        PRIMARY KEY (book, bookreferenced)  ,
        FOREIGN KEY (book) REFERENCES BookPublish(book)
      );
  
      //INSERT INTO BookReference VALUES('Math-1','Introduction to Math');
      //INSERT INTO BookReference VALUES('Physics','Foundation of Physics');
      //INSERT INTO BookReference VALUES('Chemistry','Introduction to Chemistry');
      //INSERT INTO BookReference VALUES('Biology','Molecular Biology');
      //INSERT INTO BookReference VALUES('Economics','Micro-Economics');
      //INSERT INTO BookReference VALUES('Engineering','Engineering-1');
  </code></pre>