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
Last activity: Sep 28, 2023, 08:09 PM