Let’s take a moment to discuss the real power of a relational database. Once you’ve created a view with the count by category, and a second view displaying the count for the entire table, you can join them together to reveal information never before apparent. For instance, what percentage of the books are in each category?
In this query, two views are listed in the FROM clause, but they are not joined in a WHERE clause. Why not? In this particular case, no WHERE clause is necessary because one of the views, BOOK_COUNT, will only return one row (as shown in the previous listing). The one row in BOOK_COUNT is joined to each row in CATEGORY_COUNT, yielding one row of output for each row in CATEGORY_COUNT. The same results could have been obtained by directly joining the BOOKSHELF table with the BOOK_COUNT view, but as you can see, the query is more complicated and difficult to understand—and as the number of groups expands, the query will grow even more cumbersome:
Notice the percentage calculation:
Because this result is part of a grouping function, each of the values must be grouped. Therefore, an initial attempt such as this would fail because BOOK_COUNT is not grouped:
Because there is only one row in the BOOK_COUNT view, you can perform a MAX function on it to return that single row, grouped by itself.
To create queries that compare one grouping of rows with another grouping of rows, at least one of the groupings must be a view or an “inline view” created in the FROM clause of the query. Beyond this technical restriction, however, it is just simpler and easier to understand doing the queries with views. Compare the last two examples, and the difference in clarity is apparent. Views hide complexity.
To use the inline view method, put the view’s text within the FROM clause and give its columns aliases there:
In this example, the BOOK_COUNT view has been removed from the FROM clause and replaced by its base query. In that query, the BOOK_COUNT alias is given to the result of a COUNT(*) performed against the BOOKSHELF table. In the main query, that BOOK_COUNT alias is then used as part of a calculation. Using this coding method, there is no need to create the BOOK_COUNT view. Be careful when working with multiple grouping levels within the same query—creating views commonly helps to simplify the creation and maintenance of the code.
Leave a Reply