GROUP BY clause with alias?

问题:

Does anyone know why I am not able to group TotalSales in this query and if so how can I fix this:

select coalesce(Author_ID, 'All Authors') as Author_ID
, case when Author_ID  is null then ' ' else coalesce(Book_ID, 'All Books') end as Book_ID
, TotalQuantity  
, coalesce(TotalSales, 'No Sales') as TotalSales   
from (    
     select  author_id as Author_ID 
            , book_id as Book_ID
            ,  sum(quantity) as TotalQuantity  
            ,  sum(quantity * order_price) as TotalSales   
            from a_bkinfo.book_authors   
            join a_bkorders.order_details using (book_id)
            where author_sequence = 1           
            group by Author_id, Book_ID, TotalSales with rollup
     ) tbl;

I wanted to include 'no sales' under TotalSales when an author has no book sales. Here is the updated version. I'm not positive it is correct but I do have output which seems to solve the problem. Here it is:

select coalesce(Author_ID, 'All Authors') as Author_ID
, case when Author_ID is null then ' ' else coalesce(Book_ID, 'All Books') end as Book_ID 
, NumOrders 
, coalesce(TotalSales, 'No Sales') as TotalSales
     from ( select author_id as Author_ID 
            , book_id as Book_ID
            , count(Distinct order_id) AS NumOrders
            ,(Select sum(quantity * order_price) from a_bkorders.order_details) as TotalSales
         from a_bkorders.order_headers
         join a_bkorders.order_details using (order_id) 
         join a_bkinfo.book_authors using (book_id)  
        where author_sequence = 1        
         group by Author_ID, Book_ID, TotalSales with rollup) tbl;

回答1:

UPDATED2

1. It looks like you don't need to include TotalSales in GROUP BY. Looking at your query it just doesn't make any sense. Just ditch it from the inner select.

2. To include books that have not been sold you have to use an outer join

That being said your query might look like

SELECT COALESCE(author_id, 'All Authors') author_id
     , COALESCE(book_id, IF(author_id IS NULL, 'All Books', 'Subtotal')) book_id
     , COALESCE(total_quantity, 'No books') total_quantity
     , COALESCE(total_sales, 'No Sales') total_sales   
 FROM 
(    
 SELECT author_id 
      , b.book_id 
      , SUM(quantity) total_quantity  
      , SUM(quantity * order_price) total_sales   
   FROM book_authors b LEFT JOIN order_details d
     ON b.book_id = d.book_id
  WHERE author_sequence = 1           
  GROUP BY Author_id, Book_ID WITH ROLLUP  -- you don't need TotalSales here
) q;

Sample output:

+-------------+-----------+----------------+-------------+
| author_id   | book_id   | total_quantity | total_sales |
+-------------+-----------+----------------+-------------+
| 1           | 1         | 12             | 278.50      |
| 1           | 3         | No books       | No Sales    |
| 1           | Subtotal  | 12             | 278.50      |
| 3           | 2         | 5              | 75.75       |
| 3           | Subtotal  | 5              | 75.75       |
| All Authors | All Books | 17             | 354.25      |
+-------------+-----------+----------------+-------------+

Here is SQLFiddle demo

回答2:

This is how that works (order):

1. FROM clause
2. WHERE clause
3. GROUP BY clause
4. HAVING clause
5. SELECT clause
6. ORDER BY clause

In this case your alias created on 5 step, buy where works on 2 step. You need to create subquery with calculated TotalSales and then use GROUP in outer query.