Sample Header Ad - 728x90

How to use aggregate column in where clause when using group by

2 votes
1 answer
111 views
I've following table
CREATE TABLE books (
  book_id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(100) DEFAULT NULL,
  author_fname varchar(100) DEFAULT NULL,
  author_lname varchar(100) DEFAULT NULL,
  released_year int(11) DEFAULT NULL,
  stock_quantity int(11) DEFAULT NULL,
  pages int(11) DEFAULT NULL,
  PRIMARY KEY (book_id)
)
Trying to find out the author's first and last book release year only for authors who have more than one book. Following is the query
SELECT author_lname,
       MIN(released_year) first_release,
       MAX(released_year) last_release,
       COUNT(*) book_count,
       MAX(pages) max_page_count
FROM books
GROUP BY author_lname
ORDER BY book_count DESC;
But I can't use book_count in where clause so I can do book_count > 1 I'm looking for an explanation of why this is not possible & then how to get the expected result.
Asked by Md. A. Apu (123 rep)
Sep 28, 2023, 06:38 PM
Last activity: Sep 28, 2023, 08:09 PM