2

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.

2
  • 1
    Check out the having clause.
    – mustaccio
    Sep 28, 2023 at 18:44
  • Hi, and welcome to dba.se! It might be an idea to head over to dbfiddle.uk and construct a fiddle - some sample data and desired result? I don't know why somebody voted to close this question. For me, it's a perfectly valid question.
    – Vérace
    Sep 28, 2023 at 19:03

1 Answer 1

3

Why it is not possible

The WHERE clause applies conditions to filter rows one row at a time. The expression in the WHERE clause can make reference only to columns of a joined row. That is, any column from the multiple table(s) being joined, but not any aliases for columns formed by expressions in the select-list, and not any aliases for columns formed by aggregates over multiple rows.

Despite the SELECT clause appearing first in the query syntax, expressions of the select-list are evaluated after rows are filtered out by conditions in the WHERE clause.

Likewise, aggregate expressions in the select-list are only applied to the subset of rows that pass the conditions in the WHERE clause. Therefore the WHERE clause must filter out rows first.

How to achieve what you want

A couple of methods can solve this.

One is to use a derived table subquery. The outer query can apply conditions in its WHERE clause to filter out rows that are produced by the subquery, after the subquery does its aggregation and aliases and so on.

SELECT *
FROM (
    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
) AS derived
WHERE derived.book_count > 1
ORDER BY book_count DESC;

It should make no difference to ORDER BY inside a subquery, unless the subquery also uses LIMIT.

Another method is to use HAVING which is like WHERE in that it filters rows, but it functions after the aggregation is done, and select-list expressions have been evaluated. Think of HAVING as filtering groups, whereas WHERE filters individual rows.

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
HAVING book_count > 1
ORDER BY book_count DESC;

Which method is better?

I prefer to use HAVING where possible, because the derived table may have to create a temporary table to hold results, and temp tables can be a performance drag.

But there can be cases where using a derived-table subquery is necessary anyway. Perhaps you want to join the result of the subquery to another table, for example.

2
  • In most DBMSs you would need HAVING COUNT(*) > 1 you can't just use the alias. Sep 29, 2023 at 0:55
  • 1
    @Charlieface, Yes, but the OP tagged their question mysql and MySQL permits aliases to be used in the HAVING clause. Sep 29, 2023 at 1:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.